David Morrison's SQL Blog

When is English, not English…..?

When you’re working in SQL Server obviously!

Introduction

I recently came across an interesting (I use “interesting” subjectively) situation, we had a stored procedure that, when run on a dev server, produced the desired results but when run on the live server returned no results. It didn’t error, it just didn’t return any results. The purpose of the stored procedure was effectively to take a passed in date and return all rows from a specific table that feel into the same calendar working week. After some investigation it turns out that the dev server had the users login set up with the default_language of “English” (which is actually American English …) and the live server had default_language of “British English”.

As a little side note, the default language for a login is set when the login is created, if you don’t specify a default language, the default language from the sql server install is used. To see what your current language is, you have to use a combination of two little bits of code as below:

select @@Langid
exec sys.sp_helplanguage

The first part simply returns an ID number, which correlates to the “langid” column that is returned by the stored proc. This language then in turn, amongst other things, affects two very important connection based options for working with dates in sql server, @@DATEFIRST and dateformat. So lets take a look at what these both are and why they are important when working with dates in sql server.

@@Datefirst

Lets start with @@DATEFIRST, this tells sql server which day of the week (Monday, Tuesday Wednesday etc.) is the beginning of the week. It is simply a number between 1 and 7 that represents the days of the week in the following order:

  1. Monday
  2. Tuesday
  3. Wednesday
  4. Thursday
  5. Friday
  6. Saturday
  7. Sunday

This is important as in a lot of cases the default value for this, as set by your default language, isn’t Monday as some may expect. Below is a table illustrating the various @@DATEFIRST values for each language.

image

As you can see, by default, its always either 1 or 7 (Monday or Sunday). The default for “US English” is 7 where as the default for “British English” is 1. You can see what your current @@DATEFIRST is by running this simple bit of code:

select @@DATEFIRST

and you can change it, for the current session by using the SET command as below

SET DATEFIRST {Number | @NumberVar}

Or of course you can modify your login to have different default language.

Dateformat

And then there’s dateformat. This tells sql server how to interpret and convert dates passed as strings into sql server, for example a value of  dmy, as you would expect is day month year. It doesn’t however affect the display of dates stored in sql server or how they are stored.

So for example if you pass a date as a string into a stored procedure, lets say '01-02-2010', as I’m from England, in the real world I would expect this to be February 1st 2010, sounds reasonable, doesn’t it?! Now what if your dateformat is “mdy” which is the default for “US English”? Well then sql server would interpret this value as January 2nd 2010, but as its still a perfectly valid date it would accept the value! You might never know its happened until a red faced someone from sales comes as asks you why their big sale that they just made didn’t show in the monthly sales report.

You can change the date format using set dateformat dmy (Note the lack of quotes around the dmy part) but this can be a bit of a minefield for a number of reasons, for example the date format ydm is not supported by the date, datetime2 and datetimeoffset data types. Just be aware what impact this will have when / if you do it. You can also change both settings by using the set language command but I’m not going to go into that now, have a look in BOL for yourself if you’re interested

Back to our story

So that’s a overview on datefirst and dateformat and why it’s quite important you know what they are, but back to my “Interesting” situation. So if you’ll remember we had a stored proc that took in a date as a string and returned all rows within the same calendar week. To illustrate, I’ve put together a little stored proc using adventure works as below

CREATE procedure Sales.GetOrdersInWeek
    @OrderDate smalldatetime
as
    declare
@BeginOfWeek smalldatetime
    declare
@EndOfWeek smalldatetime 
    
    
   set
@BeginOfWeek =dateadd(dd, -(datepart(dw,@OrderDate) -2) ,@OrderDate)
       
  
set @EndOfWeek =dateadd(dd,4,@BeginOfWeek)

  
SELECT*
  
FROM Sales.SalesOrderHeader as SOH 
   where OrderDate >=@BeginOfWeek
        and OrderDate <=@EndOfWeek
GO

The idea behind the code being to return all rows where the order date is in the same week as the date passed in via the @OrderDate parameter. The bulk of the “smarts” in this proc is the line that sets the @BeginOfWeek variable, so lets dissect that line (At this point I’d like to caveat that this may not be the “best” or “smartest” way to get a week beginning but it works and demonstrates the point I’m trying to communicate well) 

So starting from the inside out (as this is how, logically, it should be calculated), we have the following datepart(dw,@OrderDate). Passing the “dw” value as the first parameter of the datepart function returns the weekday (based on our current @@DATEFIRST value) of our passed @OrderDate parameter.

So for example lets say we have a language of “US English” which gives us a @@DATEFIRST of 7. Now lets also say we select the value from datepart passing in the dw parameter and the date of ‘01-25-2011’ (Note the month, day, year date format, this is due to having US English as our language), which is a Tuesday. This would return a value of 3. “Why?” I hear you cry! Well because a datefirst value of 7 tells SQL Server the beginning of each calendar week is Sunday, which makes Sunday weekday 1, hence making Tuesday weekday 3, Simples!

The next level of our nesting, (datepart(dw,@OrderDate) -2), as you can see, then subtracts two from the value of our DW datepart, you’ll see why, and why this is very important in a second.

The next and final level, dateadd(dd, -(datepart(dw,@OrderDate) -2) ,@OrderDate), takes the result from above, turns it into a negative number, and “adds” (which as any maths professor will tell you, actually subtracts Smile) that resulting number of days, using the “dd” parameter value, from our @OrderDate parameter, giving us the calendar “start” of the week

Worked Example

So lets work this through, assuming a language of “US English” with all its associated default attributes.

We pass in a date of ‘01-25-2011’ (25th January 2011, which is a Tuesday), the inner datepart(dw) gives us 3, the next section of that subtracts 2 from this so now we have 1, then we turn this into a negative number and “add” (subtract) that number of days from the passed in date, which gives us ‘01-24-2011’ which is the Monday and calendar beginning of that week!

The logic works exactly the same if you pass any date in for the given week, it always resolves back to the Monday for that calendar week.

To get the @EndOfWeek we then just add 4 days to the @BeginOfWeek to get the Friday for that calendar week

Why –2 you say……?

So back to the “–2” bit of the @BeginOfWeek construct. If you followed the logic through, you’ll see very quickly that the –2 figure is totally dependant on the fact that the datefirst is 7 and should datefirst change it would break the logic of our code and we would start getting back all kinds of seemingly odd results, and this was what was causing the problem in the stored procedure as I mentioned at the beginning of this article.

So how do we combat this? We could change this figure to match the @Datefirst value, but who’s @Datefirst? What if there are many users all with different language and hence different DateFirst values?

The solution (yay!)

My solution was to do the following to procedure:

CREATE procedure [Sales].[GetOrdersInWeek] 
    @OrderDate smalldatetime
as
    declare @BeginOfWeek smalldatetime
    declare @EndOfWeek smalldatetime
    declare @DF int

    --get the current @@DATRFIRST for preservation
    select @DF = @@DATEFIRST
    
    --set the datefirst to 7, this way we always know what it is
    set datefirst 7
    
    --now do the date work, knowing our datefirst is 7    
    set @BeginOfWeek = dateadd(dd, -(datepart(dw,@OrderDate) - 2) , @OrderDate)
        
    set @EndOfWeek = dateadd(dd, 4, @BeginOfWeek)

    --set @@DATEFIRST back to whastever it was when we started 
    set datefirst @DF

    SELECT * 
    FROM Sales.SalesOrderHeader as SOH
    where OrderDate >= @BeginOfWeek 
        and OrderDate <= @EndOfWeek

    

So what I’ve done is added a new int variable called @DF, I’ve then at the top of the proc selected the current @@DATEFIRST into this variable. After that I have set the datefirst to 7, that way I know what my datefirst is and dont have to worry about it being different. Then at the end of the code I’m setting the datefirst again, to the value of the @DF variable effectively putting it back to what it was when we started!

 

And that’s it really! Doing that solved our issue. I hope you enjoyed this post and got something out of it

Comments

David Wimbush said:

This is a good thing to highlight. It's also a serious flaw in sp_help_revlogin, which is the offially recommended method of copying a login to another server (support.microsoft.com/.../246133). I added the default language support and submitted it via the KB article's feedback about a year ago but there's still no sign of it. If anyone wants a copy it's here: sqlblogcasts.com/.../copying-logins-to-another-server.aspx.

# April 11, 2011 2:28 PM

Mark Broadbent said:

Hey Dave, good first presentation at SQLBits, nice one and look forward to seeing you there again.

...Secondly get yourself on Twitter!

# April 13, 2011 12:53 PM