SQL Server conversion issue with datetime

Question

I always use this code for conversion in datetime:

 DECLARE @a datetime
 SET @a= CONVERT(datetime,'2012-12-28 14:04:43') 
 print @a

But this does not work anymore! I tried even restarting SQL Server, but the problem remains:

The error in the image is in Italian. In English should be:

The conversion of a char data type to datetime resulted in a datetime value that is out of range of allowed values​​.

Answer 1

There are many formats supported by SQL Server - see the MSDN Books Online on CAST and CONVERT. Most of those formats are dependant on what settings you have - therefore, these settings might work some times - and sometimes not.

The way to solve this is to use the (slightly adapted) ISO-8601 date format that is supported by SQL Server - this format works always - regardless of your SQL Server language and dateformat settings.

The ISO-8601 format is supported by SQL Server comes in two flavors:

• YYYYMMDD for just dates (no time portion); note here: no dashes!, that's very important! YYYY-MM-DD is NOT independent of the dateformat settings in your SQL Server and will NOT work in all situations!

or:

• YYYY-MM-DDTHH:MM:SS for dates and times - note here: this format has dashes (but they can be omitted), and a fixed T as delimiter between the date and time portion of your DATETIME.

This is valid for SQL Server 2000 and newer.

If you use SQL Server 2008 or newer and the DATE datatype (only DATE - not DATETIME!), then you can indeed also use the YYYY-MM-DD format and that will work, too, with any settings in your SQL Server.

Don't ask me why this whole topic is so tricky and somewhat confusing - that's just the way it is. But with the YYYYMMDD format, you should be fine for any version of SQL Server and for any language and dateformat setting in your SQL Server.

So in your concrete case - use this:

DECLARE @a datetime
SET @a= CONVERT(datetime, '2012-12-28T14:04:43') 
print @a

and this should work on any SQL Server installation, with any language and date format settings.

If you run your original code for US English - it will work just fine:

SET LANGUAGE English

DECLARE @a datetime
SET @a= CONVERT(datetime, '2012-12-28 14:04:43') 
print @a

Dec 28 2012  2:04PM

but if you use Italian (or German, or British, or French) as your language, it will fail because the format without the T in the middle of the date/time string is NOT language-independent and not "safe" :

SET LANGUAGE Italian

DECLARE @a datetime
SET @a= CONVERT(datetime, '2012-12-28 14:04:43') 
print @a

Msg 242, Level 16, State 3, Line 4
La conversione di un tipo di dati varchar in datetime ha generato un valore non compreso nell'intervallo dei valori consentiti.

Answer 2

You are trying to convert a string to a datetime. Problem is with the date part. Best way is to get the date string into ISO format (yyyymmdd) and then convert. Try this;

DECLARE @a datetime
SET @a= CONVERT(datetime,replace('2012-12-28 14:04:43', '-','')) 
print @a