1

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 ?

0

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)

  • I ended up using the SET LANGUAGE command for workaround to handle the update statements that come in and will change them to use ISO format in the long run. However thanks for taking care. – Magier Nov 14 '16 at 18:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.