Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Finding the Correct Weekday Regardless of DateFirst

By Divya Agrawal,

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'.

Different DateFirst settings
DateFirst Starting day of week DATEPART(dw,'2009-12-17') DATEPART(dw,'2009-12-17')
+ @@DATEFIRST
(DATEPART(dw,'2009-12-17')
+ @@DATEFIRST)%7
7 Sunday 5 12 5
6 Saturday 6 12 5
5 Friday 7 12 5
4 Thursday 1 5 5
3 Wednesday 2 5 5
2 Tuesday 3 5 5
1 Monday 4 5 5

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.

Total article views: 6646 | Views in the last 30 days: 4
 
Related Articles
FORUM

Issue with trying to incorporate the following SELECT DATEPART(WEEKDAY, GETDATE())

Issue with trying to incorporate the following SELECT DATEPART(WEEKDAY, GETDATE())

FORUM

Finding the Correct Weekday Regardless of DateFirst

Comments posted to this topic are about the item [B]Finding the Correct Weekday Regardless of DateFi...

FORUM

DATEFIRST setting oddity

DATEPART( WK, @date) confusion...

FORUM

TSQL copy data in weekdays

TSQL copy data in weekdays

Tags
datefirst    
date's    
weekday    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones