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.
Hi
Just to say thanks to you as it really did help a lot, it was sucha stupid thing to fix
Thank you a lot! I have spent 2 hours scratching my head, but it was so easy… arrhh
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.
plz tell,
how to change the language in sql server 2005
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.
Thanks a lot .I works
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.
Thanks for the tip, Jason, I didn’t think of that.
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
Now it works! Thanks for sharing your ideas on char data type.
Thanks for the tip! I figured it out in an hour.
Nice post! Thanks for the tip. It worked.
It was very helpful, thanks.
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.
It was very helpful…. I just came across same situation….
Thanks for the post, it helped me with UK/US date format conversion.
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..
Your post saved me a lot of time. Thank you, sir! 🙂