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​​.

Accepted Answer

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.

SQL Server conversion issue with datetime

Solution

Problem