DateTime vs DateTime2时间范围差异 [英] DateTime vs DateTime2 Time Range Discrepancy

问题描述

在查看 DateTime 和 DateTime2 之间的差异时，我注意到已弃用的 DateTime 数据类型的奇数时间范围为 00：00：00.000 到 23：59：59.997 ，而 DateTime2 数据类型的限制范围是 00：00：00.0000000 到 23： 59：59.9999999 。

Looking over the differences between DateTime and DateTime2, I noticed that the deprecated DateTime datatype has an odd time range of 00:00:00.000 through 23:59:59.997, whereas DateTime2 data type has a limit of 00:00:00.0000000 through 23:59:59.9999999.

我推测此限制可能与较早的限制有关，有人可以解释为什么存在此限制？

I speculated that this limitation likely has something to do with earlier limitations, could someone please explain why this limitation is in place?

推荐答案

奇怪的限制和行为（例如最后一位始终为0、3或7）是因为时间部分在内部存储在 DATETIME ：

The weird limit and behavior (such as the last digit always being 0, 3 or 7) is because of how the time portion is internally stored in DATETIME:

时间部分存储为 ticks的数量从00:00:00开始（ 1 和 2 ）。每个滴答精确地是秒的1/300 。请记住， DATETIME 类型仅能存储以下值：

The time portion is stored as the number of ticks since 00:00:00 (1 and 2). Each tick is exactly 1/300^th of a second. Keeping this in mind, the DATETIME type is only capable of storing values such as:

00:00:00.000 -- 0 ticks
00:00:00.003 -- 1 tick,      1/300 = 0.003333 sec
00:00:00.007 -- 2 ticks,     2/300 = 0.006666 sec
00:00:00.010 -- 3 ticks,     3/300 = 0.01 sec
00:00:00.997 -- 299 ticks, 299/300 = 0.996666 sec
00:00:01.000 -- 300 ticks, 300/300 = 1 second

因为不可能存储一半或季度刻度， DATETIME 不能存储或表示介于两者之间的值。例如，如果您尝试存储诸如 CAST（'2000-01-01 00：00：00.999'AS DATETIME）之类的值，则将其舍入为 2000-01-01 00：00：01.000 。

Since it is not possible to store half or quarter ticks, DATETIME cannot store or represent the values in between. For example, if you try to store a value such as CAST('2000-01-01 00:00:00.999' AS DATETIME) it will be rounded to 2000-01-01 00:00:01.000.

这篇关于DateTime vs DateTime2时间范围差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！