0

I want to convert this time_stamp column (nvarchar50) into datetime column in SQL server. the value of time_stamp is "2018-02-16 13:30:27+09:00". I don't know which datetime code should I use to convert it. Can you help?

This is what I tried:

select convert(datetime,time_stamp, 110)  from table;
select convert(datetime,time_stamp, 120)  from table;
Response
  • 61
  • 1
  • 1
  • 8
  • There may be a way to do this, but an equally convenient way would be to just request your timestamps in UTC time, with no timezone component. – Tim Biegeleisen Feb 19 '18 at 07:42
  • Check below link https://stackoverflow.com/questions/8119386/how-to-convert-sql-servers-timestamp-column-to-datetime-format – Saurabh Feb 19 '18 at 07:50

3 Answers3

1

It is failing because of the timezone embedded in the string. However, it will work if you remove the timezone using string function such as LEFT.

SELECT CONVERT(DATETIME, LEFT(time_stamp, 19), 110)  
FROM tableName

Here's a Demo.

John Woo
  • 258,903
  • 69
  • 498
  • 492
  • When converting to Datetime , the Style parameter in `Convert( Datetime , Value [, Style ])` function is redundant. SQL server doesn't have any styles for datetime values they are stored in ticks in the database engine behind the scenes. – M.Ali Feb 19 '18 at 08:00
0

There is timezone offset in your sample date. If we want to ignore timezone offset then we can use below code -

declare @x nvarchar(50) = '2018-02-16 13:30:27+09:00'
select convert(datetime,convert(datetimeoffset, @x))
DatabaseCoder
  • 2,004
  • 2
  • 12
  • 22
0
Declare @dt NVARCHAR(100) = '2018-02-16 13:30:27+09:00'

select CAST(SWITCHOFFSET(TODATETIMEOFFSET( LEFT(@dt , 19) ,RIGHT(@dt, 6)),0) AS DATETIME)

Returns:

2018-02-16 04:30:27.000
M.Ali
  • 67,945
  • 13
  • 101
  • 127