DATEFIRST

DATEFIRST
Today I’m going to do a little rambling about the SQL Configuration Setting DATEFIRST. I was prompted to review this topic by a response from Joe stating that code I had posted from Maurice had an error. At first I thought Joe was incorrect, and that he had not taken into account the DATEFIRST configuration of his SQL Instance. Before I go any further, let me say that I believe Joe is correct in his assessment.

Joe writes:
I found that the code in the original cross-join email of either the 15th or 16th didn’t work correctly until I changed it as below. ([% 7 not in (0,6) ] to [% 7 not in (0,1) ]).

Here is a snippet of the query Joe is referencing.

With
L0 AS (select 1 as c union all Select 1 )
, L1 as (select 1 as C From L0 A Cross JOIN L0 B )
, L3 as (select 1 as C From L1 A Cross JOIN L1 B )
, L4 as (select 1 as C From L0 A Cross JOIN L3 B )
, nums as (Select ROW_NUMBER() OVER (Order by C) as nb from L4)
, monthStart as (Select dateadd(mm, datediff (mm, '19000101', GETDATE()), '19000101') as Start)
, NbOfDayInAMonth as (Select nb-1 as DayOrderInTheMonth from nums where nb <= 31)
SELECT …
WHERE (DATEPART(dw, M.Start + NDM.DayOrderInTheMonth) + @@DATEFIRST) % 7 not in (0,1)

I did some testing of the code Maurice sent to see what Joe was experiencing. I thought at first that the reason was the fact that SQL Server has the ability to have the user configure what is the first day of the week. Using the TSQL function SET DATEFIRST 7 sets Sunday as the first day of the week. Sunday is the default setting for a SQL Server installation.

I was thinking Joe simply had a different configuration for DATEFIRST. Then I noticed Maurice took the DATEFIRST configuration into account, because he adds the system variable @@DATEFIRST into the comparison before calculating the Modulus of 7 (% 7) to determine if a day is a weekend day. I made the changes Joe recommended, and things are working as expected.

So, today I want to take a quick moment to explain what is going on with the DATEFIRST configuration and property of SQL Server. Using the SET DATEFIRST command you can use any of the values from the following table.

Here is a simple demonstration from a default installation of SQL Server…

SELECT DATEPART(dw, '5-19-2013') as DOW
,@@DATEFIRST AS DateFirst

The query results in

Maurice is trying to determine for each day if it is Saturday or Sunday using the Modulus operation (%) with the number 7, since there are 7 days in a week. He uses the DATEPART function with the parameter dw returning the day of the week for a specific date. He adds to this value the current DATEFIRST Configuration before the modulus of 7 to account for different configurations.

Here is a table I did for 7 days starting Sunday the 5th of 2013. It breaks out the different parts of the calculation so you can see what is going on.

If I change the first day of the week to any other day, I always get the results in the table demonstrated above. From this result I have determined Joe has the correct solution. We need to check for NOT IN (0, 1) to get the correct results. Since all the values are going to be a positive number we could improve the query with ((DATEPART(dw, M.Start + NDM.DayOrderInTheMonth)) % 7) > 1 because the values we don’t want are 0 and 1.

If you want more detail on DATEFIRST see

SET DATEFIRST – http://msdn.microsoft.com/en-us/library/aa259210(v=sql.80).aspx
@@DATEFIRST – http://msdn.microsoft.com/en-us/library/ms187766.aspx

Thanks Joe and Maurice. Great stuff, as always…

Cheers,

Ben

$$SWYNK$$

Featured Article(s)
Designing and Implementing Online Analytical Processing (OLAP) Architecture – (Part 1)
In this part, you will learn OLAP database design, options for data warehouse schema design, and key considerations for designing relational data warehouse schema for OLAP applications.

Featured White Paper(s)
How to Use SQL Server’s Extended Events and Notifications to Proactively Resolve Performance Issues
read more)