Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server conversion issue with datetime

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:

Conversion error

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

like image 247
GVillani82 Avatar asked Dec 28 '12 17:12

GVillani82


People also ask

How do I fix conversion failed when converting date and time from character string in SQL?

To solve your issue you can use the ISO-8601 date format that is supported by SQL Server. The ISO-8601 format is supported by SQL Server comes in two flavors: YYYYMMDD for just dates. YYYY-MM-DDTHH:MM:SS for dates and times.

How can convert date and time in SQL Server?

We can convert the Date into Datetime in two ways. Using CONVERT() function: Convert means to change the form or value of something. The CONVERT() function in the SQL server is used to convert a value of one type to another type. Convert() function is used to convert a value of any type to another datatype.

Can dates and times be converted in SQL?

SQL Server provides a number of options you can use for formatting a date/time string in SQL queries and stored procedures either from an input file (Excel, CSV, etc.) or a date column (datetime, datetime2, smalldatetime, etc.) from a table. One of the first considerations is the actual date/time value needed.


2 Answers

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.

like image 80
marc_s Avatar answered Nov 15 '22 19:11

marc_s


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
like image 30
Kaf Avatar answered Nov 15 '22 18:11

Kaf