Datepart DW Giving Inconsistent Results
Published: Sep 04, 2016
I would imagine that most people tend to write “set nocount on” at the start of most code blocks and procedures as standard these days but, having been caught out with this recently, I have found a new “set” command that I’ve started to add to my code as well.

This is the “DateFirst” command and without it I’ve had some very strange results in a couple of procs I wrote and initially couldn’t work out why. It turns out that it was down to my using the day of the week “datepart(dw” alongside unexpected language settings.

Quite simply, if you write:

select datepart(dw, current_timestamp)

Then you will get a different result depending on the language settings of your SQL Server.

The problem is that in European setup (so, using British English or French, for example) the first day of the week is Monday, but in the US they class Sunday as the first day of the week.

If you want to see what your current setting is, you can run the following:

select @@datefirst

If this returns 7 then Sunday is the first day of the week, but if it returns 1 then it’s Monday.

This doesn’t sound massively important, but what if you want to remove weekends from a table of records?

In US setting, we have Sunday as the first day of the week, Monday second etc which means that we can write the following:

select *
from myTable
where datepart(dw, myDate) not in (7, 1)

However, this would not work in the UK with European settings in which we would need to write:

select *
from myTable
where datepart(dw, myDate) < 6

As you can see, this is where problems can arise if you run generic code without checking the settings of the SQL Server.

To avoid this we can change the setting at the start of the proc in order to ensure that the code works as expected every time.

Ensuring your code runs in European format we need:

set datefirst 1

And for US we use

set datefirst 7

It’s that simple, and because of this I now tend to add this statement to any generic code in which I’m using dates, just to ensure consistency.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London



Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron