I am on the home stretch with my writing of short articles or tidbits on Transaction SQL date/time functions.
We already talked about functions like DATEPART() that returns the day of the week as an integer and DATENAME() that returns the day of the week as text.
But what is the first day of the week? The default for the US English language is 7, or Sunday. However, this setting differs between countries.
Today, I will be talking about @@DATEFIRST, a configuration function which returns the current session value and SET DATEFIRST, a set statement which changes the value for the current session (SPID).
The examples below are sample calls to these functions. Their impact if felt when a developer uses the DATEPART() and DATENAME() functions.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
/* Sample use of function */ -- Reset to default SET DATEFIRST 7; GO -- Show setting (US default -- 1=mon ... 7=sun) SELECT @@DATEFIRST AS 'Current Setting' GO -- Today is saturday SELECT @@DATEFIRST AS 'First Day', DATEPART(dw, GETDATE()) AS 'Today''s Number', DATENAME(dw, GETDATE()) AS 'Today''s Name'; GO -- Update to new value? SET DATEFIRST 6; GO -- Today is saturday SELECT @@DATEFIRST AS 'First Day', DATEPART(dw, GETDATE()) AS 'Today''s Number', DATENAME(dw, GETDATE()) AS 'Today''s Name'; GO |
The output from the test is listed below.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
output: Current Setting --------------- 7 First Day Today's Number Today's Name --------- -------------- ------------------------------ 7 1 Sunday First Day Today's Number Today's Name --------- -------------- ------------------------------ 6 2 Sunday |
To recap this article, the @@DATEFIRST configuration function tells you what day is the first one of a given week. This value is controlled at the session level. The SET DATEFIRST statement can be used to override this value.