CODEWITHSUNDEEP
sql-server-2008type-conversion
Kasun Jayasinghe
Kasun Jayasinghe

Reputation: 355

Convert an ISO formatted date to DATETIME

I am writing a SQL query using SQL Server Management Studio and there are some NVARCHAR type values in ISO date format (example: 20130302T164800). I need to convert them to a DATETIME

I tried the Convert() function but it causes an exception:

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value

Is there a way I can do this?

Upvotes: 2

Views: 8756

Answers (1)

Ian Preston
Ian Preston

Reputation: 39566

The problem is that your string is not an accepted SQL Server datetime format. SQL Server recognises the ISO8601 format, which is:

yyyy-mm-ddThh:mi:ss.mmm

Which would be 2013-03-02T16:48:00 for your date above.

See Date And Time Styles section.

So the following statement will fail:

declare @date nvarchar(max) = '20130302T164800'

select convertedDate = cast(@date as datetime)

If you convert the string to the ISO8601 format, the statement will work:

declare @date nvarchar(max) = '2013-03-02T16:48:00'

select convertedDate = cast(@date as datetime)

SQL Fiddle with demo.

You can update your format to one SQL Server recognises and cast the string to a datetime in one statement:

declare @date nvarchar(max) = '20130302T164800'

select cast(left(@date, 4)
  + '-' + substring(@date,5,2)
  + '-' + substring(@date,7,5)
  + ':' + substring(@date,12,2)
  + ':' + substring(@date,14,2) as datetime)

SQL Fiddle with demo.

This is just an example, you could convert it to any format recognised by SQL Server, but this converts it to ISO8601. Basically, convert it to a different format to allow the conversion to work.

Upvotes: 6

Related Questions