SQL DATEPART(dw,date) need monday = 1 and sunday = 7

Solution 1:

This will do it.

SET DATEFIRST 1;

-- YOUR QUERY

Examples

-- Sunday is first day of week
set datefirst 7; 
select DATEPART(dw,getdate()) as weekday


-- Monday is first day of week
set datefirst 1;
select DATEPART(dw,getdate()) as weekday

Solution 2:

You can use a formula like:

(weekday + 5) % 7 + 1

If you decide to use this, it would be worth running through some examples to convince yourself that it actually does what you want.

addition: for not to be affected by the DATEFIRST variable (it could be set to any value between 1 and 7) the real formula is :

(weekday  + @@DATEFIRST + 5) % 7 + 1

Solution 3:

You can tell SQL Server to use Monday as the start of the week using DATEFIRST like this:

SET DATEFIRST 1

Solution 4:

I would suggest that you just write the case statement yourself using datename():

select (case datename(dw, aws.date)
             when 'Monday' then 1
             when 'Tuesday' then 2
             when 'Wednesday' then 3
             when 'Thursday' then 4
             when 'Friday' then 5
             when 'Saturday' then 6
             when 'Sunday' then 7
        end)

At least, this won't change if someone changes the parameter on the day of the week when weeks begin. On the other hand, it is susceptible to the language chosen for SQL Server.