Sql-server – SQL Server 2008 Convert String to Datetime question

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

There is too much precision in the varchar to be converted into datetime.

One option(better in my opinion) would be to change the target column to datetime2(7). Then you can convert like this:

declare @dt varchar(50)
set @dt = '2015-12-02 20:40:37.8130000'

select cast(@dt as datetime2(7));

If changing the column is not an option the you can do the conversion like this:

declare @dt varchar(50)
set @dt = '2015-12-02 20:40:37.8130000'

select cast(cast(@dt as datetime2(7))as datetime)