CODEWITHSUNDEEP
sqlsql-serverdatetime
Mewtwo
Mewtwo

Reputation: 1311

Convert datetime string in datetime format in SQL server

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.

Upvotes: 1

Views: 2816

Answers (4)

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

Try this

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

Upvotes: 2

etsa
etsa

Reputation: 5060

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)

Upvotes: 0

Mansoor
Mansoor

Reputation: 4192

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)

Upvotes: 0

Rajesh Bhat
Rajesh Bhat

Reputation: 811

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

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

Upvotes: 0

Related Questions