SQL Server error 242 with ANSI datetime
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.