I'm trying to debug a MS SQL Server error 242:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The error is originated by this statement:
CREATE TABLE db.schema.test (
Column1 datetime NULL
) GO
INSERT INTO db.schema.test (Column1)
VALUES (convert(varchar,convert(datetime,{D '2019-06-30'}),102));
This is my server version:
Microsoft SQL Server 2017 (RTM-CU15) (KB4498951) - 14.0.3162.1 (X64)
May 15 2019 19:14:30
Copyright (C) 2017 Microsoft Corporation
Standard Edition (64-bit) on Linux (Debian GNU/Linux 9 (stretch))
And the language @@LANGUAGE
is Italiano
.
The same statements works on
Microsoft SQL Server 2017 (RTM-CU10) (KB4342123) - 14.0.3037.1 (X64)
Jul 27 2018 09:40:27
Copyright (C) 2017 Microsoft Corporation
Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)
With language: us_english
Without without making explicit convert style it works
INSERT INTO db.schema.test (Column1)
VALUES (convert(varchar,convert(datetime,{D '2019-06-30'})));
Convert style 102 is ANSI yyyy.mm.dd
, shouldn't be recognized by both English and Italian SQL Server?
Best Answer
Starting from the inside, the ODBC escape sequence
{D '2019-06-30'}
returns adatetime
.(ignoring the redundant convert to
datetime
)You're then converting that to a string with 102 style (rather than 105 for Italian).
You're then relying on an implicit conversion back to
datetime
to match the type of the target column.The implicit conversion has a default style of 0 as you can see in the execution plan:
(note: you should always specify the maximum length when using
varchar
)When you use style 102
yyyy.mm.dd
you must also setDATEFORMAT
toYMD
so SQL Server can parse the format under style 0.When you use style 105
dd-mm-yyyy
, you must setDATEFORMAT
toDMY
for the same reason.The reason it works on one and not the other is the default
DATEFORMAT
for the language in each case.See datetime,
SET LANGUAGE
, and Write International Transact-SQL Statements in the documentation.Also SQL Server DateTime Best Practices by Aaron Bertrand.