CODEWITHSUNDEEP
sqldatetimesql-server-2016
Arjun
Arjun

Reputation: 165

How to convert ISO 8601 utc time to datetime format in SQL server 2016

I'm trying to import some data from a flat file into SQL SERVER 2016. In the flat file, there is a date data in ISO 8061 UTC format

eg: 2017-10-08T00:00:00.0000000Z

While creating the table in sql server, I created the column as datetime. But now when I try to do a select on this particular column it is throwing error as:

Expected data type: DATETIME NOT NULL, Offending value: 2017-10-08T00:00:00.0000000Z  (Column Conversion Error), Error: Conversion failed when converting the NVARCHAR value '2017-10-08T00:00:00.0000000Z' to data type DATETIME.

Is there anyway to resolve this. I tried the below statement in SQL server:

SELECT convert(datetime, DateColumnISO, 127) FROM myTable;

This again threw the same error.

Thanks, Arjun

Upvotes: 2

Views: 8390

Answers (1)

iamdave
iamdave

Reputation: 12243

You have too much precision to convert to a datetime. Either strip out everything past milliseconds or convert to a datetime2:

select convert(datetime, '2017-10-08T00:00:00.000Z'    ,127) as ConvertedToDateTime
      ,convert(datetime2,'2017-10-08T00:00:00.0000000Z',127) as ConvertedToDateTime2

Output:

+-------------------------+------------------------------+
|   ConvertedToDateTime   |     ConvertedToDateTime2     |
+-------------------------+------------------------------+
| 2017-10-08 00:00:00.000 | 2017-10-08 00:00:00.0000000  |
+-------------------------+------------------------------+

Upvotes: 4

Related Questions