This article would help one to get the correct weekday regardless of the DateFirst setting on your SQL Server instance. DateFirst is a type of week day setting in SQL Server that denotes the start of the week. You can customize the starting day of the week with this setting.

If your application is dealing with real time data showing the dates and their week days as per the English calendar, there could be a big problem if the DateFirst setting is changed. This can even result in a nightmare if by mistake anyone changes the DateFirst setting as all the queries using date calculations will change and it would be very difficult for one to identify the root cause of the problem.

If you had ever handled shopping malls offers, there are often special offers on weekends sent to the daily customers. A reminder is sent on each weekend to the customers inviting them to shop at discount rate. Lets say the setting is done on the basis of DateFirst like:

IF (DATEPART(dw,'2009-12-17') = 7)
SendReminder

But if the setting of DateFirst is changed, the weekends will be shown as different days if a date calculation is used. It is always good to write queries that handle weekdays calculations regardless of the DateFirst setting.

Let us first understand the concept of DateFirst. We consider the week as per the English calendar as to be starting from Sunday and ending on Saturday. The weekdays are numbered as:

Monday = 1

Tuesday = 2

Wednesday = 3

.....

Sunday = 7

If DateFirst = 7 then Sunday which is the seventh day of the week is considered first day of the week. By default in Language 'us_english' the DateFirst setting is 7.

Similarly, if DateFirst = 2 then Tuesday which is the second day of the week is considered to be the first day of the week. Due to this setting, if you will see the weekday for any date say '2009-12-17' which is actually the 5th day of the week, will be instead shown as 3rd day of the week. Here, is the problem as the week starting day is considered as 'Tuesday' and so after three days from Tuesday is our date '2009-12-17' which is Thursday.

Check the same thing for DateFirst setting as 7. It will show you the exact weekday as 5 for the date '2009-12-17'.

SET DATEFIRST 2
SELECT DATEPART(dw,'2009-12-17') AS date
SET DATEFIRST 7
SELECT DATEPART(dw,'2009-12-17') AS date

Our goal is to get the day of the week for the date '2009-12-17' as '5' regardless of the DateFirst setting.

Let's go towards deriving the solution for this problem. There is a system function, @@DATEFIRST, which returns the current value of DATEFIRST. We can use this function to get our correct day of the week.

Due to the change in DateFirst, our week starts from a different day compared to normal case of Sunday/monday as in case of DateFirst setting of 2. How many days the week start goes delayed? Can we say the week start has been delayed for 2 days, which is the value of @@DATEFIRST, the current value of DateFirst. So, in order to make the correct day of the week we need to add the value of @@DATEFIRST in the dayofweek which we are getting.

SET DATEFIRST 2
SELECT (DATEPART(dw, '2009-12-17') + @@DATEFIRST) AS dw

SET DATEFIRST 7
SELECT (DATEPART(dw, '2009-12-17') + @@DATEFIRST) AS dw

We added the value of @@DATEFIRST to the dayofweek in the above statement. But, we have a total of seven days in a week and the dayofweek cannot be greater than that, as in the case of adding @@DATEFIRST to a datepart dayofweek having DATEFIRST setting as 7. So, we need to do modulus 7 to the above output.It will give you the correct weekday.

SET DATEFIRST 2
SELECT (DATEPART(dw, '2009-12-17') + @@DATEFIRST) % 7 AS dw

SET DATEFIRST 7
SELECT (DATEPART(dw, '2009-12-17') + @@DATEFIRST) % 7 AS dw

Let us summarize different DateFirst setting for date '2009-12-17'.

This is what we need. Its very easy to get, just a tricky logic. I hope this post would be helpful to the readers. Now anywhere in your date calculations, weekday comes in picture, just dont worry about the DateFirst setting and write query to fetch the weekday as shown above.