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.

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?

Solution:

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.

Region

 

 

Hope this helped … as I was going mental for a while there!!!