sql-server - SQL Server 2019 正在返回查询中日期时间条件不匹配的记录

Question

我刚刚遇到了一个绝对出乎意料的问题。

我有一个非常原始的表，它把 Unix 时间戳和 DateTime 放在一起。表格脚本如下：

CREATE TABLE [dbo].[Hist]
(
    [time] [bigint] NULL,
    [pair] [varchar](20) NULL,
    [DateTime] AS (DATEADD(SECOND, ROUND([time] / (1000000000), (0)), CONVERT([datetime], '1970-01-01',(120)))) PERSISTED
) ON [PRIMARY]
GO

所以输出是这样的：

time                DateTime
-------------------------------------------
1609946564379305356 2021-01-06 15:22:44.000
1609946558002360563 2021-01-06 15:22:38.000
1609946555348782291 2021-01-06 15:22:35.000
1609946554681771971 2021-01-06 15:22:34.000
1609946552162363862 2021-01-06 15:22:32.000
1609946551615328953 2021-01-06 15:22:31.000
1609946551546017601 2021-01-06 15:22:31.000
1609946551508916896 2021-01-06 15:22:31.000

但是当我在某些时间条件下查询此表时，它会返回不匹配的记录：

SELECT TOP (1000) 
    [time],
    [DateTime]
FROM 
    [EE_2].[dbo].[Hist]
WHERE
    [DateTime] < CONVERT(datetime, '2021-01-06 15:22:34.000')
ORDER BY
    [DateTime] DESC

time                DateTime
--------------------------------------------
1609946703969897863 2021-01-06 15:25:03.000
1609946702008782869 2021-01-06 15:25:02.000
1609946702625778619 2021-01-06 15:25:02.000
1609946702622227680 2021-01-06 15:25:02.000
1609946700549524407 2021-01-06 15:25:00.000

然而，将运算符从'<'更改为'>'根本不返回任何记录，即使有对应的匹配项，即服务器认为所有记录都早于条件中指定的任何条件，无论它是什么条件

问题是特定于机器的，因为同一个表和同一个数据库在其他机器上运行良好。

有没有人有什么建议在哪里看？一些本地化设置或其他什么？我已经没有想法了。

标签： sql-server

Answer 1

DateTime 格式有两种不同的形式。

1. 对于数据类型 DATETIME 和 SMALLDATETIME，您需要使用短标准 ISO SQL 格式 YYYYMMDD hh.mmm.ss.nnn
2. 对于数据类型 DATETIME2、DATE、TIME、DATETIMEOFSSET，您需要使用大型标准 ISO SQL 格式 YYYY-MM-DD hh.mmm.ss.nnn

我赌 1 百万美元你有一个 DATETIME 所以你需要使用

**[DateTime] < '20210106 15:22:34.000'**

为什么 ？

DATETIME 和 SMALDATETIME 已创建，而 SQL ISO 版本是 SQL 2 (1986)，通常使用 YYYYMMDD 日期部分格式。

DATETIME2、DATE、TIME、DATETIMEOFSSET 创建于 2008 年，而 SQL 3（又名 SQL:1999）由 ISO 委员会发布，它更改了格式以更好地适应其他相关语言，如 XML。

顺便说一句，DATETIME 和 SMALDATETIME 是不推荐使用的数据类型，不应在 2008 年之后创建的数据库中使用...

---

作为 ISO SQL 格式 short 或 long 的测试：

SET LANGUAGE English;

DECLARE @D DATETIME = '20211231', @D2 DATETIME2 = '2021-12-31';
SELECT @D, @D2;
GO

SET Language Français;

DECLARE @D DATETIME = '20211231', @D2 DATETIME2 = '2021-12-31';
SELECT @D, @D2;
GO

SET Language Russian;

DECLARE @D DATETIME = '20211231', @D2 DATETIME2 = '2021-12-31';
SELECT @D, @D2;
GO