Sql-server – Why do those Update statements fail using a datetime format

date formatdatetimesql server

Given:

Query #1:

begin tran
UPDATE [dbo].[t] SET [mydatetime]='2011-12-25 07:00:00.000';
rollback

Query #2: (changing month/day) 

begin tran
UPDATE [dbo].[t] SET [mydatetime]='2011-25-12 07:00:00.000';
rollback

Why does the QUERY #1 fail to run on a new installed SQL Server while query #2 runs succesfully?

Msg 242, Level 16, State 3, Line 6 Bei der Konvertierung eines
varchar-Datentyps in einen datetime-Datentyp liegt der Wert außerhalb
des gültigen Bereichs.

In comparison why does Query #1 run on the old server while query #2 fails on the new server?

Msg 242, Level 16, State 3, Line 7 The conversion of a varchar data
type to a datetime data type resulted in an out-of-range value.

The servers have different versions (2008 / 2016). And as you can see from error messages there are two different languages installed. Please don't tell me just because the language of the Sql-Server installation is different it is not able to handle different date formats?

Is this configurable?
Will I get away with uniform formats like 20150129 08:00:00.000 ?

Best Answer

You could deal with the issue by specifying the style and doing an explicit CONVERT() from string to datetime rather than an implicit conversion. https://msdn.microsoft.com/en-us/library/ms187928.aspx

UPDATE dbo.t SET mydatetime = CONVERT('2011-12-25 07:00:00.000', 121)

Related Question