我有一个 Microsoft SQL Server 2008 R2 数据库，其中DateTime列不是datetime数据类型，而是varchar(50). 我们的网站记录了这些数据，而我并没有真正控制它（我确信一个合适的datetime列会更好）。我正在尝试进行查询，以获取一些年初至今的结果BETWEEN DateTime field AND NOW()。
SQL Server 表：
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查询：
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
我收到一个错误
从字符串转换日期和/或时间时转换失败
此查询有效...
SELECT CONVERT(datetime, SUBSTRING('2014-01-01T00:00:00-06:00', 1, 19)) AS Expr1
我也试过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
这给出了相同的转换错误，但这有效......
SELECT CAST(SUBSTRING('2014-01-01T00:00:00-06:00', 1, 19) AS datetime) AS Expr1
我确信我缺少一些基本的东西，但它似乎应该工作。
我做错了什么？
JJ