VAAA VAAA - 7 months ago 31
SQL Question

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

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

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.