T-SQL Tuesday #99: Part 1 - Date/Time Habits

T-SQL Tuesday Logo

This month's T-SQL Tuesday is hosted by Aaron Bertrand, who gave us a choice: write about something we're passionate about outside of SQL, or write about one of the SQL bad habits/best practices he's posted about in the past. I have to admit, I was sorely tempted to post about geckos or Ingress... but it felt like a bit of a cop-out when I have a grand total of one technical post to my name.

So I'm doing two posts! First of all, here's a post about some bad SQL habits... you can find part 2 here.

I really enjoy working with SQL Server, but sometimes working with dates and times drives me up the wall. Some of that is due to the inherent complexity of dealing with this type of data, some is down to needing to understand how SQL Server deals with dates and times. Regardless of the cause there's some complexity here, and they're something we all have to use regularly - it's well worth making sure you understand best practices as well as how SQL Server handles things.

This is an area where it's very easy to have code fail silently - you can be getting the wrong results, and never realise. And even worse, it's an area where the way your code works can change based on settings.

Aaron has as excellent summary post covering a few of his date/time related bad habits and best practices (#BackToBasics : Dating Responsibly), and I agree completely with the points he makes:

  • Use a language-safe string format (always yyyymmdd - no hyphens!)
  • Let the client format dates, rather than doing it in T-SQL
  • Don't use shorthand in date functions (e.g. month instead of m)
  • Don't use BETWEEN

If any of those sound surprising to you, read the article in full - and make sure you catch that note about using DateAdd() instead of simply adding/subtracting from dates!

But past this, I think there's more that's worth keeping in mind. Aaron has already highlighted the impact of language settings on how a string can be interpreted when being converted to a date - but it can also impact the results you get from some of the date/time related functions - such as DatePart(). This Microsoft docs page explains how week and weekday are affected. Similarly, DateName() is affected.

Note that the datefirst and dateformat settings are defaulted based on language, but can also be set separately. You can use sp_helplanguage to see what default settings are used for each language - or you can have a look in sys.syslanguages.

I ran into this in some T-SQL which used DatePart(weekday, yyyymmdd) - while checking the results I found that everything had been offset by a day. It turned out that the default language for the instance was American English, not British English - it was using Sunday as the first day of the week, while I expected Monday.

Even if you're careful to install with the right settings, this can catch you out if you use someone else's code that's been written expecting different settings, or if you have to work on third party databases - I've run across an instance that uses Spanish language settings (and collation) because the vendor's developers are based in Spain.

Wherever possible, it's best to use T-SQL that will work regardless of language settings - like Aaron's yyyymmdd rule. But if you still have some T-SQL that's dependent on one of these settings, you can set them explicitly for that session:

set language [British];
set datefirst 1;
set dateformat dmy;

You can find the possible settings for these in the Microsoft docs:

Going back to my American English vs. British English problem, I could have simply accepted that weeks start on Sunday on that instance - but then what happens when someone gets around to correcting the language setting? What if the default language setting gets changed, but some logins are still using the old default language, while others use the new one? So in addition to the bad habits Aaron has written about, I'd add using date functions that are dependent on certain settings without enforcing those settings at the session level.