YYYY-MM-ddTHH:mm:ssZ Format Error

Question

Post reply

YYYY-MM-ddTHH:mm:ssZ Format Error

TheComedian

SSCrazy

Points: 2932
More actions
August 2, 2016 at 12:40 am

#328593

Hi,
I'm trying to insert a date with the YYYY-MM-ddTHH:mm:ssZ format into a field with the type datetime but I'm getting an error: Conversion failed when converting date and/or time from character string.
Even if I run the following query I still get the same error:
SELECT CAST('2016-08-01T07:04:24+0000' AS datetime)
I know that this can be converted into a string with the following query but my question why isn't this recognized as a proper datetime and how can I get the table to accept it?
SELECT CONVERT(VARCHAR(50),'2016-08-01T07:04:24+0000', 127)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

alexander.oechsle Default port Points: 1439 More actions · Answer 1

use the following form:

YYYYMMDD HH:mm:ss.ms

this works fine for us.

--> Date without Separator-Character

Regards

Alex

TheComedian SSCrazy Points: 2932 More actions · Answer 2

Thanks for the reply but unfortunately this format is coming from an inbound file that I have no control over. Furthermore the client insists this is the "right format" and doesn't want to change it.

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 3

Quick thought, datetime does not have any time zone awareness and only assumes UTC, convert into a time zone aware data type such as datetime2 instead.

😎

SELECT CONVERT(DATETIME2,'2016-08-01T07:04:24+01:00',127)

alexander.oechsle Default port Points: 1439 More actions · Answer 4

OR

SELECT REPLACE(REPLACE(REPLACE('2016-08-01T07:04:24+0000', '-', ''), 'T', ' '), '+000', '')

--> Take the disturbing characters away...

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 5

alexander.oechsle (8/2/2016)
OR
SELECT REPLACE(REPLACE(REPLACE('2016-08-01T07:04:24+0000', '-', ''), 'T', ' '), '+000', '')
--> Take the disturbing characters away...

This will skew the value if the time zone is not UTC!

😎

TheComedian SSCrazy Points: 2932 More actions · Answer 6

Eirikur Eiriksson (8/2/2016)
Quick thought, datetime does not have any time zone awareness and only assumes UTC, convert into a time zone aware data type such as datetime2 instead.
😎
SELECT CONVERT(DATETIME2,'2016-08-01T07:04:24+01:00',127)

So there has to be a colon in between the 1 and the 0? +01:00 instead of +1000? Because I'm getting the latter instead of the former and the following code without the colon doesn't work.

SELECT CONVERT(DATETIME2,'2016-08-01T07:04:24+0100',127)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

TheComedian (8/2/2016)
Eirikur Eiriksson (8/2/2016)
Quick thought, datetime does not have any time zone awareness and only assumes UTC, convert into a time zone aware data type such as datetime2 instead.
😎
SELECT CONVERT(DATETIME2,'2016-08-01T07:04:24+01:00',127)
So there has to be a colon in between the 1 and the 0? +01:00 instead of +1000? Because I'm getting the latter instead of the former and the following code without the colon doesn't work.
SELECT CONVERT(DATETIME2,'2016-08-01T07:04:24+0100',127)

That's correct:

DECLARE @StringDate VARCHAR(40) = '2016-08-01T07:04:24+0000'

SELECT CONVERT(DATETIME2,@StringDate,127)

GO

DECLARE @StringDate VARCHAR(40) = '2016-08-01T07:04:24+00:00'

SELECT CONVERT(DATETIME2,@StringDate,127)

You could use STUFF like this:

DECLARE @StringDate VARCHAR(40) = '2016-08-01T07:04:24+0000'

SELECT CONVERT(DATETIME2,STUFF(@StringDate,23,0,':'),127)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden