Wednesday, October 14, 2015

Day of week calculation

With SQL Server you can get the day in week using this simple code:

SELECT DATEPART(weekday, getdate())
This gives you a number from 1 to 7, corresponding to the day in the week.

But which day is the first day in the week?

If you are European - it's Monday.
If you are American - it's Sunday.
If you are SQL Server - it depends...

There's a setting in SQL Server that controls this behavior and perhaps it is not set to your liking. And perhaps you don't want to change it and run the risk of side-effects - or perhaps you simply don't have the permissions.

Despair not!

This simple code will allow you to get the day in week independent of SQL Server's settings:

European:
SELECT (DATEDIFF(dd, DATEADD(YEAR, 0, 0), getdate()) + 1) % 7

American:
SELECT (DATEDIFF(dd, DATEADD(YEAR, 0, 0), getdate()) + 2) % 7