主页 / dba / 问题 / 62712
Accepted
doubleJ
Asked: 2014-04-08 12:02:54 +0800 CST

SQL Server：在查询中从 ISO8601 转换为 DateTime

  • 772

我有一个 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

sql-server type-conversion

3 个回答

  1. Best Answer

    问题是您不能CONVERT或ISO8601 日期时间与.CASTVARCHARDATETIME

    从 SQL Server 2008 开始，DATETIMEOFFSET引入了数据类型来处理带有偏移的日期时间。

    正如在其他地方回答的那样，您需要将CASTDateTimeVARCHAR列添加到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
    • 4

  2. 你为什么不直接使用？：

    WHERE DateTime >= '2014-01-01'
  AND DateTime < CONVERT(CHAR(19), DATEADD(second, 1, GETDATE()), 126)

    优点：
    -varchar列中的值不会被转换，因此不会出现错误
    - 可以使用索引的效率

    缺点：
    - 不处理时区偏移量
    - 一些值虽然作为日期时间无效（例如'2014-01-33T25:61:62', '2014 Sunday 13th of April'），但它们仍将由查询返回。

    • 2

  3. 尝试将类型转换datetimeoffset为如下类型：

    CAST(datetime as DATETIMEOFFSET)

    例如，您的查询将变成如下所示：

    SELECT *
FROM dbo.RebroadcastSmoothStreaming
WHERE (CAST(datetime as DATETIMEOFFSET)
BETWEEN CAST('2014-01-01T00:00:00-06:00' AS datetimeoffset) 
AND dateadd(day, 1, SYSDATETIMEOFFSET()))
ORDER BY Ip
    • 1

相关问题