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:
```sql
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
```sql
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?
TopAnswers
Databases
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:
```none
[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/