This week I was working on a migration script where I needed to run multiple sql queries. Since I was going to use this only once, I decided to use SqlHelper classes to connect and run sql to MsSql2012. Part of this included inserting and updating a date into the database. On my local computer this was working fine however, when placed on the server, I kept on getting the below error:
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.
A sample of the Sql I was trying to use was:
UPDATE Table SET StartDate = '26-Mar-15 2:53:42 PM' WHERE ID = 15
I googled this error and amongst the top suggestions/solutions I found:
- sql query is passing the wrong date format so it goes out of range
- you should not pass DateTime values as sting in your SQL statement. Instead use parameters such as (
cmd.Parameters.AddWithValue("@mvendid", mVendid); )
None of the 2 above worked. I tried all sorts of formats (“dd/mm/yyyy hh:mm:ss”, “mm/dd/yyyy hh:mm:ss”, “yyyy/mm/dd hh:mm:ss”, etc etc). I tried also declaring a new date in the sql and saving it, which still gave same error. I was about to give up when I though what about the computer’s date?
We checked my computer’s date format from “Control Panel -> Region and Language -> Formats” and noticed that the short date was completely different. Since I was desperate, we tried changing the server’s local date to match my date. Just like a miracle it worked fine.
Hope this helped … as I was going mental for a while there!!!