Convert datetime string in datetime format in SQL server

Question

I'm using MS SQL server and I have a date field of type text. The dates stored there are in this format

2017-03-01T18:23:02+0700

I'm trying to convert this field in a datetime field but I fail. I have tried

CONVERT(datetimeoffset,date, 127)
CONVERT(datetime,date, 127) 
CONVERT(datetime2,date, 127)

but I keep getting

Conversion failed when converting date and/or time from character string.

I think the problem is that according to ISO8601 the time offset must be in the format hh:mm while mine is hhmm. I don't mind keeping only the date (yyyy-mm-dd) if it is more easy.

I have read similar question but none matches exactly my case and I can't figure out the solution.

if you only need date then use this select left('2017-03-01T18:23:02+0700',10) — Shiju Shaji
– Shiju Shaji, Commented Mar 22, 2017 at 9:14

Krishnraj Rana · Accepted Answer · 2017-03-22 09:26:32Z

2

Try this

Declare @dt varchar(50)
set @dt = '2017-03-01T18:23:02+0700'
select convert(datetime, replace(LEFT(@dt, LEN(@dt) - 1), '+', '.'), 126)

answered Mar 22, 2017 at 9:26

Krishnraj Rana

6,6583 gold badges33 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rajesh Bhat · Accepted Answer · 2017-03-22 09:22:15Z

0

If you need only date part then you can use below query

SELECT CAST(LEFT('2017-03-01T18:23:02+0700',10) as DATE)

answered Mar 22, 2017 at 9:22

Rajesh Bhat

8214 gold badges10 silver badges21 bronze badges

Comments

Mansoor · Accepted Answer · 2017-03-22 09:22:38Z

0

Use Below query to convert datetime :

SELECT CONVERT(DATETIME,REPLACE(REPLACE('2017-03-01T18:23:02+070','T',' 
'),'+','.'),103)

For DATE only use below query :

SELECT CONVERT(DATE,REPLACE(REPLACE('2017-03-01T18:23:02+010','T',' 
 '),'+','.'),102)

answered Mar 22, 2017 at 9:22

Mansoor

4,1921 gold badge19 silver badges27 bronze badges

Comments

etsa · Accepted Answer · 2017-03-22 09:25:22Z

0

It doesn't seem to work in both ways (both raise error):

SELECT CONVERT(datetime,'2017-03-01T18:23:02+0700',127)
SELECT CONVERT(datetime,'2017-03-01T18:23:02+07:00',127)

It seems that it works only specifying Z as time zone:

SELECT CONVERT(datetime,'2017-03-01T18:23:02Z',127)

answered Mar 22, 2017 at 9:25

etsa

5,0501 gold badge9 silver badges18 bronze badges

Collectives™ on Stack Overflow

Convert datetime string in datetime format in SQL server

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related