SQL Server - Date Danger, Sql Server

Language: Layout:

--------------------------------------------------------------------------------
-- 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;

View schema
Execution time: 0,02 sec, rows selected: 10, rows affected: 0, absolute service time: 0,17 sec