Datepart DW Giving Inconsistent Results
Published: 15th December 2014
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.
Comments:
NB: Comments will only appear once they have been moderated.