Lesson learned. My usual date time literal:
YYYY-MM-DD HH:MM:SS.nnn
happens to be a bad practice since it is interpreted differently by the language property of the current login.
This sample code shows how it interprets a date with poor format by different languages.
DECLARE @datePoorFormat AS VARCHAR(23);
SET @datePoorFormat = ‘2017-02-12 23:12:12.123’;
DECLARE @DateTable AS TABLE (temp1 DATETIME)
SET LANGUAGE us_english;
INSERT INTO @DateTable(temp1) VALUES (@datePoorFormat);
SET LANGUAGE british
INSERT INTO @DateTable(temp1) VALUES (@datePoorFormat);
SELECT temp1 FROM @DateTable
The result of the query is
2017-02-12 23:12:12.123 — Feb 12
2017-12-02 23:12:12.123 — Dec 02
Please use the neutral formats. For DATETIME type these are:
‘YYYYMMDDHH:MM:SS.nnn’
‘YYYY-MM-DDTHH:MM:SS.nnn’
‘YYYYMMDD’
I like the second one because hyphens emphasizes date parts better in your code.
BTW language config is set by default at your current login. The picture below shows where you can set it via GUI.
