SQL Server – How to CONVERT From ISO8601 to DateTime Within Query

castsql servertype conversion

I have a Microsoft SQL Server 2008 R2 database where the DateTime column is not a datetime data type, but varchar(50). Our website logs that data and I don't really have control over it (I'm sure a proper datetime column would be preferable). I'm trying to do a query that gets some year-to-date results with BETWEEN DateTime field AND NOW().

SQL Server table:

Id  DateTime                    Ip              City            State       Country         Continent   UserAgent
-----------------------------------------------------------------------------------------
1   2013-02-05T17:58:45-06:00   68.55.34.32     Laurel          Maryland    United States   North America   NULL
2   2013-02-05T17:58:52-06:00   70.88.57.62     Port Charlotte  Florida     United States   North America   NULL
3   2013-02-05T17:59:06-06:00   71.3.202.110    Cape Coral      Florida     United States   North America   NULL
4   2013-02-05T17:59:23-06:00   67.239.18.161   Naples          Florida     United States   North America   NULL
5   2013-02-05T17:59:58-06:00   96.19.107.135   Gulfport        Mississippi United States   North America   NULL

SQL query:

SELECT *
FROM dbo.RebroadcastSmoothStreaming
WHERE 
   (DateTime BETWEEN CONVERT(datetime, SUBSTRING('2014-01-01T00:00:00-06:00', 1, 19)) AND { fn NOW() })
ORDER BY Ip

I'm getting an error

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

This query works…

SELECT CONVERT(datetime, SUBSTRING('2014-01-01T00:00:00-06:00', 1, 19)) AS Expr1

I've also tried CAST()…

SELECT *
FROM dbo.RebroadcastSmoothStreaming
WHERE (DateTime BETWEEN CAST(SUBSTRING('2014-01-01T00:00:00-06:00', 1, 19) AS datetime) AND { fn NOW() })
ORDER BY Ip

That gives the same conversion error, but this works…

SELECT CAST(SUBSTRING('2014-01-01T00:00:00-06:00', 1, 19) AS datetime) AS Expr1

I'm sure that I'm missing some fundamental bit, but it seems like it should work.

What am I doing incorrectly?

JJ

Best Answer

The issue is that you cannot CONVERT or CAST a VARCHAR ISO8601 datetime with an offset to a DATETIME.

From SQL Server 2008 onwards, the DATETIMEOFFSET datatype was introduced to handle datetimes with offsets.

As answered elsewhere, you would need to CAST your DateTime VARCHAR column to a DATETIMEOFFSET

SELECT *
FROM dbo.RebroadcastSmoothStreaming
WHERE 
   (
       CAST(DateTime AS DATETIMEOFFSET) BETWEEN 
         CAST('2014-01-01T00:00:00-06:00 AS DATETIMEOFFSET) 
         AND { fn NOW() }
   )
ORDER BY Ip
Related Question