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

Question

I have a Query where I get the WeekDay of a date but by default:

• Sunday = 1

• Moday = 2

• etc.

The function is:

DATEPART(dw,ads.date) as weekday 

I need the result so:

• Sunday = 7

• Monday = 1

• etc.

Is there any shortcut to do this? Or I will have to do a CASE statement?

Answer 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 

Answer 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