CODEWITHSUNDEEP
sql-serverdatetime-format
Dave Hendrik
Dave Hendrik

Reputation: 15

How to convert string contain timezone to datetime sql server

I have string that contain date time value 2014-09-09T10:30:04+0700 and i want to convert to datetime format in SQL Server

previously i try with this query, but it doesn't works

SELECT CONVERT(DATETIME, '2014-03-01T10:30:04+0700', 126)
CONVERT(datetimeoffset, '2014-03-01T10:30:04+0700', 127)

how to convert the string to sql server datetime format?

Upvotes: 1

Views: 8920

Answers (2)

Edper
Edper

Reputation: 9322

The SELECT CONVERT(DATETIME, '2014-03-01T10:30:04+0700', 126) fails because of the Timezone offset:

+0700

So, you need to use LEFT or SUBSTRING to remove that, like:

SELECT CONVERT(DATETIME, LEFT('2014-03-01T10:30:04+0700',19), 126)

And the CONVERT(datetimeoffset, '2014-03-01T10:30:04+0700', 127) also fails because your Timezone offset is wrong, namely:

From

+0700

it should be

+07:00

Upvotes: 4

Geoduck
Geoduck

Reputation: 9009

SQL Server wants a colon in the tz offset. You can do:

CONVERT(DATETIMEOFFSET, LEFT('2014-03-01T10:30:04+0700',22) + ':00')

but you need to be sure the tz offset is there. The offset should always end if 00 but if not you need to be a little more clever and get the last 2 digits as well.

Upvotes: 3

Related Questions