sql-server - 使用 DateTime2 截断日期和时间

Question

多年来我一直使用这种格式来截断日期和时间

SELECT  DATEADD(HOUR, DATEDIFF(HOUR, 0, '1980-02-05 12:45'), 0) AS Hour ,
        DATEADD(DAY, DATEDIFF(DAY, 0, '1980-02-05 12:45'), 0) AS Day ,
        DATEADD(MONTH, DATEDIFF(MONTH, 0, '1980-02-05 12:45'), 0) AS Month ,
        DATEADD(YEAR, DATEDIFF(YEAR, 0, '1980-02-05 12:45'), 0) AS Year;

但我需要存储非常早的日期，例如 1400-01-01，因此我可以使用 DateTime2。

但是我如何支持使用 DateTime2 仍然像上面那样截断的能力？

将上面的年份从 1980 更改为 1400 将导致

SELECT  DATEADD(HOUR, DATEDIFF(HOUR, 0, '1400-02-05 12:45'), 0) AS Hour ,
        DATEADD(DAY, DATEDIFF(DAY, 0, '1400-02-05 12:45'), 0) AS Day ,
        DATEADD(MONTH, DATEDIFF(MONTH, 0, '1400-02-05 12:45'), 0) AS Month ,
        DATEADD(YEAR, DATEDIFF(YEAR, 0, '1400-02-05 12:45'), 0) AS Year;

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

SO 转换为 DateTime2

SELECT  DATEADD(HOUR, DATEDIFF(HOUR, 0, CAST('1400-02-05 12:45' AS DATETIME2)),0) AS Hour ,
        DATEADD(DAY, DATEDIFF(DAY, 0, CAST('1400-02-05 12:45' AS DATETIME2)),0) AS Day ,
        DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST('1400-02-05 12:45' AS DATETIME2)), 0) AS Month ,
        DATEADD(YEAR, DATEDIFF(YEAR, 0, CAST('1400-02-05 12:45' AS DATETIME2)), 0) AS Year;

The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.

我假设 0 被视为 DateTime 数据类型并将其有效地转换为 DateTime。

尝试使用 CAST(0 AS DATETIME2) 将 0 转换为 DateTime2 给我这个错误

Explicit conversion from data type int to datetime2 is not allowed.

最后，我想将它们用作表中的持久列，该表与 DateTime 数据类型一起使用时效果很好，但与 DateTime2

一起使用时就不那么容易了

Answer 1

您应该使用一些特定的基准日期而不是 0。 0可以隐式转换成datetime类型。对于 datetime2 不允许这样的隐式转换。此外，基准日期应具有 datetime2 类型。然后 DATEDIFF 和 DATEADD 将使用 datetime2 值。

使用显式基准日期的另一个原因是您需要将此基准日期设为一年中的第一天，并且需要 00:00:00 时间才能使公式正常工作。隐式开始日期，例如 0 转换为 datetime 或 '' 转换为 datetime2 现在也具有这些属性，但是你真的想依赖类型实现的内部细节吗？最好明确说明这些内容，这样新手更容易理解公式。

此外，如果您想使用相同的方法截断到周边界，那么您必须选择一个基准日期，即星期一(如果您的一周从星期一开始)或星期日(如果您的一周从星期日开始).公式保持不变，但基准日期很重要。

示例 1 - 有效

DECLARE @VarBase datetime2 = '2000-01-01';
DECLARE @VarValue datetime2 = '1400-02-05 12:45';

SELECT
    DATEADD(HOUR,  DATEDIFF(HOUR,  @VarBase, @VarValue), @VarBase) AS Hour,
    DATEADD(DAY,   DATEDIFF(DAY,   @VarBase, @VarValue), @VarBase) AS Day,
    DATEADD(MONTH, DATEDIFF(MONTH, @VarBase, @VarValue), @VarBase) AS Month,
    DATEADD(YEAR,  DATEDIFF(YEAR,  @VarBase, @VarValue), @VarBase) AS Year;

示例 2 - 有效

SELECT
    DATEADD(HOUR,  DATEDIFF(HOUR,  @VarBase, '1400-02-05 12:45'), @VarBase) AS Hour,
    DATEADD(DAY,   DATEDIFF(DAY,   @VarBase, '1400-02-05 12:45'), @VarBase) AS Day,
    DATEADD(MONTH, DATEDIFF(MONTH, @VarBase, '1400-02-05 12:45'), @VarBase) AS Month,
    DATEADD(YEAR,  DATEDIFF(YEAR,  @VarBase, '1400-02-05 12:45'), @VarBase) AS Year;

示例 3 - 不起作用

SELECT
    DATEADD(HOUR,  DATEDIFF(HOUR,  '2000-01-01', '1400-02-05 12:45'), '2000-01-01') AS Hour,
    DATEADD(DAY,   DATEDIFF(DAY,   '2000-01-01', '1400-02-05 12:45'), '2000-01-01') AS Day,
    DATEADD(MONTH, DATEDIFF(MONTH, '2000-01-01', '1400-02-05 12:45'), '2000-01-01') AS Month,
    DATEADD(YEAR,  DATEDIFF(YEAR,  '2000-01-01', '1400-02-05 12:45'), '2000-01-01') AS Year;

Adding a value to a 'datetime' column caused an overflow.

它不起作用，因为文字 2000-01-01 被转换为 datetime，而不是 datetime2。

示例 4 - 有效

SELECT
    DATEADD(HOUR,  DATEDIFF(HOUR,  CAST('2000-01-01' AS datetime2), '1400-02-05 12:45'), CAST('2000-01-01' AS datetime2)) AS Hour,
    DATEADD(DAY,   DATEDIFF(DAY,   CAST('2000-01-01' AS datetime2), '1400-02-05 12:45'), CAST('2000-01-01' AS datetime2)) AS Day,
    DATEADD(MONTH, DATEDIFF(MONTH, CAST('2000-01-01' AS datetime2), '1400-02-05 12:45'), CAST('2000-01-01' AS datetime2)) AS Month,
    DATEADD(YEAR,  DATEDIFF(YEAR,  CAST('2000-01-01' AS datetime2), '1400-02-05 12:45'), CAST('2000-01-01' AS datetime2)) AS Year;