SQL Server date range skip weekday (Monday's)-CodePudding

I want to run a query to update rows within a date range, but I want to skip Monday's in that range. Is there a skip function or something like that.

UPDATE Database 
SET obj.SET = obj.SETvalue  
WHERE locationId = obj.avd 
  AND date >= 'obj.dateFrom' 
  AND date <= 'obj.dateTo'

CodePudding user response：

You can use DATEPART to find out if a date is a Monday or not.

In this example we use a date range from jan 01 2021 until jan 31 2021 and we leave out all Mondays.

update obj
set    obj.SET = obj.SETvalue
from   yourTable obj
  inner join ( select obj.yourID, obj.yourDateColumn
               from   yourTable obj
               where  obj.avd = locationID
               and    obj.dateFrom >= '20220101'
               and    obj.dateTo <= '20220131'
             ) t 
    on obj.yourID = t.yourID         
   and datepart(weekday, t.yourDateColumn) <> 1

What happens here is that we use a subquery that retrieves all rows within the wanted date range,
and then we join on that subquery and in the join condition we can specify that we don't like Mondays.

You should check what daynumber Monday is for you, this can vary

I believe that in the US Monday is 2
Here in europe we like to use 1 for Monday

If you have 2 for Monday but you want it to be 1,
than you can tell SQL Server to use Monday as the start of the week using DATEFIRST like this:

SET DATEFIRST 1

To know what the first daynumber you have now, you can run

select @@datefirst

EDIT

As mentioned in the comments by @LukStorms, you can use the @@datefirst variable in your statement, so you are always sure you are filtering on Mondays

and (datepart(weekday, t.yourDateColumn)   @@datefirst - 1) % 7 != 1