sgargel imported from SE
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?
Top Answer
Paul White
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][1], [`SET LANGUAGE`][2], and [Write International Transact-SQL Statements][3] in the documentation.

>Applications that use other APIs, or Transact-SQL scripts, stored procedures, and triggers should use the [CONVERT][4] 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][5] by Aaron Bertrand.

  [1]: https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql#supported-string-literal-formats-for-datetime
  [2]: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-language-transact-sql
  [3]: https://docs.microsoft.com/en-us/sql/relational-databases/collations/write-international-transact-sql-statements
  [4]: https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql
  [5]: https://www.mssqltips.com/sqlservertip/5206/sql-server-datetime-best-practices/
SQL Server error 242 with ANSI datetime

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.