Search code examples
sqlsql-serverdatetimevarchar

Error conversion varchar datetime

I have this query:

update table 
set initTime = '2012-08-20 09:30:00.000' 
WHERE id='0124'

and it says:

conversion from varchar to datetime generated an out of range value.

Can you help me out please?:(

Solution

  • To be safe, you should use a language-independent date format for your date strings:

    UPDATE dbo.table 
SET initTime = '2012-08-20T09:30:00.000' 
WHERE id = '0124'

    See the difference? I added a T between the date and the time portion. This makes this a valid ISO-8601 date/time string, and this should work regardless of what SQL Server language and regional settings you have.

    Without that T in the middle, it's not a proper ISO-8601 date/time string, and depending on your language/regional/dateformat settings in SQL Server it might be interpreted as the 8th day of the 20th month of 2012 and of course, that 20th month sorta doesn't exist..... :-)