Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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

Categories


© Copyright 2020 SQLTraining Ltd.