SQL Server 2008 VarChar To DateTime

Question

I have a table where unfortunately a number of dates are stored as strings.

I have a number of reports that cast them to datetimes and filter by them. This was working fine until today when all of a sudden i'm getting this error

"The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

The dates are all stored in the format of "yyyy-mm-dd" and are all valid.

If I run the following SQL statement

SELECT CAST('2010-06-02' AS DateTime) 

I would expect to get "2010-06-02" however as of today I'm getting "2010-02-06" something has changed with the way SQL formats dates. I've had a look in regional settings on the server and it all looks to be correct.

What else could be causing this?

Answer 1

Try setting the format explicitly

select convert(datetime, '2010-06-02',101)

Answer 2

Q1: What else could be causing this?

The local, You probably are under the local101(US) and put data from 103 (British/French)

Like barry sad use convert

Answer 3

An unambiguous way of getting this conversion is to do the following:

SELECT CAST(replace('2010-06-02', '-', '') AS DateTime) 

And that will always be interpreted as YYYYMMDD, ignoring the set dateformat ydm declaration or any cultural settings that the database has.