sql-server add tag
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 (imported from SE)
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/

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.