Converting to datetime

Converting to a datetime can be a dangerous game. If the month is specified as a word then the date format is unambiguous but very often the year, month and day are all numeric. In this case the conversion can easily be misinterpretted if done in the wrong way.

Until recently I'd thought that the YYYY-MM-DD date format was foolproof, regardless of regional differences:

SET @date = CAST('2003-04-01' AS DATETIME)

Or just

SET @date = '2003-04-01'

I discovered this week that this format is really no better than any other. A new user was created with the default language of their server login set to "British English" rather than "English", and the above conversion generated a date of 4th Jan rather than 1st Apr.

It seems that British English defaults to a date format of ydm, whereas English defaults to ymd. SET DATEFORMAT can be used to control this in some situations, but if you're doing a conversion in a function then this won't work.

A much better, and totally unambiguous, way of creating the date is to use CONVERT. I've used this for years when formatting datetime values as strings but never the other way round.

Using CONVERT it's possible to specify the exact format you're using. I've chosen 112 (YYYYMMDD) as the cleanest:

SET @date = CONVERT(DATETIME, '20030401', 112)

Published 22 September 2007 12:10 PM by jonsayce
Filed under: , ,

Comments

# Saggi Neumann said on 22 September, 2007 04:46 PM

Hi!

I also ran into this problem with a customer who installed SQL Servers in Germany and Turkey - they used what is known as the universal time format (as it is in XML), that is: yyyy-MM-dd hh:mm:ss.

Turns out that changing the default language in the server level to american english also doesn't solve this problem (unlike SET DATEFORMAT and SET LANGUAGE which do solve the problem but as you mentioned doesn't work in functions).

To make a long story short - the solution was simple: We changed the date format string to "yyyyMMdd hh:mm:ss" (simple 8 digits for date, nothing in between, a space and regular time format) - without CONVERT. Apparently, this is the ISO format and it works in all cultures and languages you can set up SQL Server in - again, without using CONVERT.

A better solution altogether would be to use parameterized stored procedures and queries, push the date variables as parameter values and let the drivers (odbc/ado/ado.net) do their magic for you, but the customer wanted a quick solution without changing the code and testing everything again.

Cheers,

Saggi

# DamianMulvena said on 27 September, 2007 10:37 AM

I thought until recently too that yyyy-mm-dd would be unambiguous, as both the US convention of mm-dd and the UK convention of dd-mm use this same format when the year is included.

I did find however that the unseparated date formats (yy)yymmdd (either 8 or 6 char) are ALWAYS interpreted unambiguously, with Microsoft documentation confirming this explicitly. I encourage developers to always follow this practice to overcome the confusion with date order.

# Madhivanan said on 03 December, 2007 02:09 PM

http://weblogs.sqlteam.com/mladenp/archive/2007/10/22/SQL-Server-The-one-and-only-locale-insensitive-date-format.aspx