THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

T-SQL Tuesday #001: Yesterday it worked, today it's not working...

But did it actually work yesterday?

If the day is Friday, Noverber 13th, or the 13th day of any other month, and your query blows up for the first time, one place to search for is character strings converted to datetime values. The following script illustrates the problem:

 -- yesterday it worked, or did it?
SET LANGUAGE US_English;
SELECT CAST('11/12/2009' AS DATETIME);


SET LANGUAGE Norwegian;
SELECT CAST('11/12/2009' AS DATETIME);

Changed language setting to us_english.

-----------------------
2009-11-12 00:00:00.000

(1 row(s) affected)

Changed language setting to Norsk.

-----------------------
2009-12-11 00:00:00.000

(1 row(s) affected)

 

-- today it's not working
SET LANGUAGE US_English;
SELECT CAST('11/13/2009' AS DATETIME);

SET LANGUAGE Norwegian;
SELECT CAST('11/13/2009' AS DATETIME);
            
 

 Changed language setting to us_english.

-----------------------
2009-11-13 00:00:00.000

(1 row(s) affected)

Changed language setting to Norsk.

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

Note that the query did not quite work yesterday either - although it did not raise errors, it was selecting the wrong data.

Published Tuesday, December 08, 2009 11:06 AM by Alexander Kuznetsov
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Adam Machanic said:

Wow! The response to the first T-SQL Tuesday was truly amazing. We ended up with 20 great posts, from

December 9, 2009 11:10 AM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement