poniedziałek, 8 lipca 2013

Normalize WEEKDAY behaviour [T-SQL]

In T-SQL, if you want to get know if it's Friday already, you have to query like this..

SELECT DATEPART(WEEKDAY, GETDAY())

And SQL Server answers with 5. Thanks GOD it's friday! But it's not..

Number given by DATEPART is dependent to SQL connection setting called @@DATEFIRST.
And default @@DATEFIRST is 7, meaning that Sunday is no. 1, Monday is no. 2... and so on..
So Friday is no. 6 in fact, not 5..

Check DATEFIRST by SELECT @@DATEFIRST.

User can trigger this setting by SET DATEFIRST. For example SET DATEFIRST 1, and now Monday is no. 1 and Friday is no. 5.

Ok..

So, DATEFIRST is related to SQL connection and everyone can have different settings. So how to write functions dependent on WEEKDAY? Can we just SET DATEFIRST in function body on ourselves? No, we cannot.

But we can normalize number returned by doing following trick:

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

Now Monday is no. 1 no matter what DATEFIRST is set to!
And Friday.. Thanks T-SQL it's 5th, again!