SQL Server error 242 with ANSI datetime

Question

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?

Answer 1

VALUES (convert(varchar,convert(datetime,{D '2019-06-30'}),102));

Starting from the inside, the ODBC escape sequence {D '2019-06-30'} returns a datetime.

(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:

[Expr1003] = Scalar Operator(CONVERT_IMPLICIT(datetime,CONVERT(varchar(30),[@1],102),0))

(note: you should always specify the maximum length when using varchar)

When you use style 102 yyyy.mm.dd you must also set DATEFORMAT to YMD so SQL Server can parse the format under style 0.

When you use style 105 dd-mm-yyyy, you must set DATEFORMAT to DMY 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.

Applications that use other APIs, or Transact-SQL scripts, stored procedures, and triggers should use the CONVERT statement with an explicit style parameter for all conversions between the time, date, smalldate, datetime, datetime2, and datetimeoffset data types and character string data types.

Also SQL Server DateTime Best Practices by Aaron Bertrand.