varchar data type to datetime data type resulted in an out-of-range??

Question

Very weird problem occurred, I have moved a site from one server to another - All is working, but any query involving a date is playing up. I get the following:

DELETE FROM MYTABLE WHERE categoryId = -2 AND datecreated < '3/23/2010';

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

Now what's strange is I have changed the LCID to 1033 on the new server as the date is showing as US format and its still throwing an error! I then tried 2057 and again the same error? Made no difference.

I'm a little confused, as this is a working site from a server with IIS6 - The locale is 1033 on that server and it works perfectly!! :S

I have just tried thrown a Cdate() around the date too and yet again the same error???

Any ideas??

Answer 1

Well, I use to express datetime varchar fields in the yyyyMMdd format, and have not had problems with that

AND datecreated < '20100323';

Answer 2

I agree that yyyyMMdd is the way to go. You might want to look at the default language for the login as well as I think that controls how this is interpreted.

Answer 3

As far as I know, formatting the dates as 'dd-mmm-yyyy', with mmm beging the three character English name for the month, dd being the number and yyyy obviously being the year, works with every database I have worked with (except some French Oracle db which needed the French month name).