I was working on a client’s server, in the process of making a site live. I restored the database from a test version of the database and copied over the codebase. Everthing was exactly the same as the test site. Still, all the date related queries were throwing this error.

[xml]

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

[/xml]

I tried looking at my code, I tried running the same query in Query Analyzer (which was not throwing errors) and I tried setting the database DATEFORMAT to dmy. But none of the above fixed the code. It got a bit frustrating when suddenly it struck to me that the only difference in the 2 databases is the SQL user that I created for live database. So I compare the 2 users’ properties. and sure enough, one of them had the default Language as ‘English’ and the other one had ‘British English’ . So, changing the default language for the SQL user solved my problem.

A very small fix but took me about 3 hours to figure this one out. I hope it helps someone out.

 

18 Responses to The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

  1. abc says:

    Hi

    Just to say thanks to you as it really did help a lot, it was sucha stupid thing to fix

  2. Semyon says:

    Thank you a lot! I have spent 2 hours scratching my head, but it was so easy… arrhh

  3. Ian says:

    Thank you very much for this. For many hours now I’ve been trying to correct this error after some functionality on my web application stopped after I migrated servers. I for one am glad it was something so easy! Again many thanks.

  4. interval says:

    plz tell,
    how to change the language in sql server 2005

    • Anuj Gakhar says:

      You can change the language by going to the database properties and it’s the last option on one of the panes on right hand side. At the very bottom, if I remember correctly.

  5. Sagar says:

    Thanks a lot .I works

  6. Jason says:

    Thanks Anuj, great tip. For the record, you can also fix this by adding a “Language=” parameter to the connection string and setting it to the correctr language.

  7. Anuj Gakhar says:

    Thanks for the tip, Jason, I didn’t think of that.

  8. Coat Rack says:

    Yes I like one time I spent two days trying to figure outa database error only to realize that I had two different versions of the English language set up

  9. Sarah Parker says:

    Now it works! Thanks for sharing your ideas on char data type.

  10. T. Charles says:

    Thanks for the tip! I figured it out in an hour.

  11. Kelly Wright says:

    Nice post! Thanks for the tip. It worked.

  12. Artur says:

    It was very helpful, thanks.

  13. Dexter Vegas says:

    Thank you.

    Your tip hinted me on the solution as I found out that the date format submitted in the query had to be exactly the same as the server.

    select getdate() will get you the server’s format.

  14. Piyush Desai says:

    It was very helpful…. I just came across same situation….

  15. Rajesh says:

    Thanks for the post, it helped me with UK/US date format conversion.

  16. Mayur says:

    I’m having same problem but it on same pc data inserted successfully on some dates but throwing error on other dates like above current date..

  17. pkaps says:

    Your post saved me a lot of time. Thank you, sir! 🙂

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

© 2011 Anuj Gakhar
%d bloggers like this: