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

Continue reading