When writing a date in a script, there are a number of ways of doing it, the server uses geographic settings to imply the format. This has some hidden dangers.

UK and US dates switch the day and month around. If there are changes in geographic settings and the date format isn’t explicit, there can be unexpected consequences. The best outcome we can hope for is that the date given isn’t valid e.g. The 12th day of the 30th month (when we wanted 30th December). The worst is when we modify the numbers for the 12th October, rather than the 10th December!

This is something to put on any migration/upgrade roadmap. Especially when migrating/consolidating projects to new environments, or combining processes in a multinational organisation.

The solutions

When we write code using dates, let’s not be ambiguous with our formatting. If we use methods which remove the need for the DBMS to guess, it can’t get it wrong.

• For SQL Server versions after 2012, use DateFromParts(YYYY, MM, DD)
• For SQL Server versions 2005 onwards, use 'DD-MON-YY'
• If you must use another format like ISO, Specify the format: CONVERT(DATE, 'YYYY-MM-DD', 120)

DateFromParts translates easily over geographic settings without ambiguity and is easy to read.

The DD-MON-YY format isn’t as ambiguous as others (England and America two nations divided by a common language and how to write dates). I tend to use it for smaller tasks and ad-hoc scripts as it’s easy to read.

The danger demonstrated

The below code samples highlight this, you can see them in action on SQL Server – Date Danger

--------------------------------------------------------------------------------
-- View the setting as it is right now
--------------------------------------------------------------------------------
SELECT [dateformat] FROM syslanguages WHERE [name] = @@LANGUAGE;


--------------------------------------------------------------------------------
-- Change to US and test
--------------------------------------------------------------------------------
SET DATEFORMAT DMY;

SELECT [dateformat] FROM syslanguages WHERE [name] = @@LANGUAGE;

-- Convert to date without specifying format

SELECT CONVERT(DATETIME, '2014-12-10') AS date_test;

-- This will cause an error
SELECT CONVERT(DATETIME, '2014-12-15') AS date_test;

-- Using a numeric argument to CONVERT to specify the date format

SELECT CONVERT(DATETIME, '2014-12-10', 120) AS date_test;

SELECT CONVERT(DATETIME, '2014-12-15', 120) AS date_test;

--------------------------------------------------------------------------------
-- Change to UK and test
--------------------------------------------------------------------------------
SET DATEFORMAT MDY;

SELECT [dateformat] FROM syslanguages WHERE [name] = @@LANGUAGE;

-- Convert to date without specifying format

SELECT CONVERT(DATETIME, '2014-12-10') AS date_test;

SELECT CONVERT(DATETIME, '2014-12-15') AS date_test;

--Using a numeric argument to CONVERT to specify the date format

SELECT CONVERT(DATETIME, '2014-12-10', 120) AS date_test;

SELECT CONVERT(DATETIME, '2014-12-15', 120) AS date_test;