SQL Server – Why storing dates as strings is a bad idea

Consider the following value: ’01-02-10′.  Is it the 1st February 2010?  Is it the 2nd of January 2010?  Is it the 10th of February 2001?

All three of those answers are potentially valid, and you can convert that string into all three:-

DECLARE @DateString CHAR(8) = '01-02-10';

SET DATEFORMAT DMY;

SELECT CONVERT(DATE, @DateString);

SET DATEFORMAT YMD;

SELECT CONVERT(DATE, @DateString);

SET DATEFORMAT MDY;

SELECT CONVERT(DATE, @DateString);

whichdateisright

71902217

So, how do we know what we’re going to get?  This is all dependent on the language settings of your login, and on your session.  Bear in mind that multiple group membership can result in having multiple different language settings.

To check what your current session’s settings are, you can run the following SQL using the sys.dm_exec_sessions dmv:-

SELECT DES.date_format
, language
FROM sys.dm_exec_sessions AS DES
WHERE DES.session_id = @@SPID

mydateandlanguage

So, for me, we’re looking at the 1st February 2010.

One way of ensuring that your string will always convert into the correct date, is by using the CONVERT function with the style option.  The full reference can be found at the CAST and CONVERT page of Books Online.  So to be sure my date always gets converted I can use the following syntax:-

DECLARE @DateString CHAR(8) = '01-02-10';

SET DATEFORMAT DMY;

SELECT CONVERT(DATE, @DateString, 5);

SET DATEFORMAT YMD;

SELECT CONVERT(DATE, @DateString, 5);

SET DATEFORMAT MDY;

SELECT CONVERT(DATE, @DateString, 5);

ConvertDatesProperly.jpg

Now we know we’ll always get the right date, regardless of any language settings or session settings.  Of course, what I’m recommending is then to actually store that data as a date in your database, please don’t leave it as a string!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s