Tuesday, 21 April 2015

SQL weekday datepart different depending on locale

Depending on which language your user is running, the following query will return a different result.

SELECT DATEPART(weekday, GETDATE())

Using the English language (US), this would return 2 if run on a Monday, as Sunday is allocated 1.
If using British English, this would return 1 for Monday, as Sunday is now allocated 7.

I've just had to write some queries that could be run using either locale. Tested locally it all worked fine. Deployed to live server, all my dates were out. Took me a while to understand that the locale of the user was different.

I Google'd for an answer and found multiple solution, the one that worked for me was the following:

SELECT (DATEPART(weekday, GETDATE()) + @@DATEFIRST - 2) % 7 + 1

There are many other solutions to this problem that may be beneficial to others.

No comments: