主页 / dba / 问题 / 338751
Accepted
questionto42
Asked: 2024-04-18 07:00:00 +0800 CST

700000 秒内的整数作为第一年的天数：如果最旧的日期时间日期是 1753-01-01，如何在 tsql 中将其转换为日期并返回？

  • 772

我发现了日期的整数格式，我也知道日期，但我不知道如何在 TSQL 中获取该日期，而且如果我有日期，我也不知道如何获取整数：

700444 -> 1918-10-02

731573 -> 2003-12-24

739479 -> 2025-08-16

这些 6 位数字适合作为从 0001-01-01 开始的每一天的计数器，我通过获取从接近 2000 年的日期开始一个世纪的天数并将其添加到 1900 来进行检查：

select DATEADD(dd,731573/20,'19000101')

出去：

2000-02-24 00:00:00.000

但我无法运行select DATEADD(dd,731573/20,'10000101')，这会抛出：

将 varchar 数据类型转换为 datetime 数据类型导致值超出范围。

Microsoft Learn 表示 TSQL 仅允许从 1753-01-01 开始的日期，请参阅datetime (Transact-SQL) Microsoft Learn，因此：

select DATEADD(dd,731573/20,'17530101')

出去：

1853-02-24 00:00:00.000

不过，我无法将 731573 添加到第一年。然后我发现1/1/1753在SQL Server中有何意义？:

--（正如其中一个答案和为什么你应该总是写“varchar”，其长度放在括号中？通常，如果不这样做，您会得到正确的结果 - DBA SE，varchar(length)而不是仅仅varchar）--

选择 CONVERT(VARCHAR, DATEADD(DAY,-731572,CAST('2003-12-24' AS DATETIME2)),100)

SELECT CONVERT(VARCHAR(30), DATEADD(DAY,-731572,CAST('2003-12-24' AS DATETIME2)),100)

出去：

Jan  1 0001 12:00AM

为了证明这一点，数字是从 0001 年第一天开始的天数。现在我想知道是否可以在不将日期时间列格式化为 datetime2 的情况下到达那里。我的日期都在 20 世纪和 21 世纪，所以我不需要datetime2. 我以日期时间形式获取数据并尝试避免类型转换。

如何将这个七千整数作为从 1 年到日期的天数计数器，以及如何在不将日期转换为 datetime2 的情况下从日期返回到该整数？

sql-server

3 个回答

  1. Best Answer

    您已正确确定 693596 是“0001 年 1 月 1 日”和“1900 年 1 月 1 日”之间的天数。了解此偏移量后，您可以直接在给定的整数表示形式和 SQL Serverdatetime类型之间进行转换。

    也就是说，这个问题和答案表明了一个重要的误解。

    现在我想知道是否可以在不将日期时间列格式化为 datetime2 的情况下到达那里

    字符串不是日期或时间。不存在“日期时间格式”或“日期时间2 格式”之类的东西。

    SQL Server接受多种字符串格式来隐式或显式转换为日期或时间类型之一。这些字符串仍然是字符串，而不是日期/时间类型。

    当这些类型需要以字符串格式显示或返回时，它们还有一个默认格式。当需要时，总是执行从内部类型到字符串的转换，但 T-SQL 语句作者通常不可见。它也可能由客户端而不是 SQL Server 执行，具体取决于具体情况。

    使用字符串表示日期和时间时，养成使用确定性风格的显式数据类型转换的习惯。否则，您通常会依赖未记录的遗留行为，这些行为可能会导致代码中出现意外结果或细微错误。

    例如，当你写：

    SELECT DATEDIFF(dd,'1753-01-01', '0001-01-01')

    您要求 SQL Server 将这些字符串隐式转换为可用的日期/时间类型之一。没有记录 SQL Server 将选择哪种数据类型以及何时选择。

    显然，它不能选择datetime字符串表示“0001-01-01”（因为这会导致错误），尽管它可能选择“1753-01-01”。至少，不精确会给服务器带来额外的工作，因为它试图解释字符串，根据某种方案选择数据类型，并执行任何必要的转换，以便为开始日期和结束日期选择的类型在计算中具有可比性DATEDIFF。

    我提到这一切是因为问题表明希望避免数据类型转换。在 T-SQL 中避免显式CAST或（更好）CONVERT并不能避免 SQL Server 执行的隐式转换。

    例如，在您的代码中：

    SELECT DATEDIFF(dd,'0001-01-01', '2003-12-24') + 1

    SQL Server 必须将这两个字符串（可能不止一次）转换为适合与DATEDIFF. 大多数时候，SQL Server 将DATETIMEOFFSET(7)在这些情况下在内部最终使用。

    事实上，内部表达式服务只有四个代码实现DATEDIFF：

    1. datetime开始日期、datetimeoffset(7)结束日期
    2. datetimeoffset(7)开始日期、datetime结束日期
    3. 两个都datetime
    4. 两个都datetimeoffset(7)

    其他任何内容（包括提供字符串）都会导致转换。

    没有人应该记住这些事情，或者解释像这样的未记录的内部细节，但这并不意味着你不会受到它们的影响。

    使用显式类型编写您的示例之一：

    -- Convert from datetime type to integer
DECLARE 
    @Base datetime = CONVERT(datetime, '19000101', 112),
    @ToConvert datetime = CONVERT(datetime, '20031224', 112),
    @Offset integer = 693596;

SELECT Result = DATEDIFF(DAY, @Base, @ToConvert) + @Offset;
GO
-- Convert from integer to datetime type
DECLARE 
    @Base datetime = CONVERT(datetime, '19000101', 112),
    @ToConvert integer = 731573,
    @Offset integer = 693596;

SELECT Result = DATEADD(DAY, @ToConvert - @Offset, @Base);

    最后，大多数人更喜欢使用明确的缩写，例如DAY代替，DD并且绝对建议不要VARCHAR在没有明确最大长度的情况下使用。

    其中一些可能看起来像是不必要的打字或冗长，但这是我希望在我开始使用 SQL Server 时得到的建议。它节省的未来调试时间比构建 T-SQL 时花费的时间要多得多。

    • 14

  2. 正如您所说，如果您想完全保持在 DATE （或 DATETIME）的范围内，那么您只需偏移整数即可将其重新设置为适当的数据类型。

    我只是想谈谈日期类型的一些背景。

    在 SQL Server 中，DATE 使用 1753-01-01 作为第一天，因为这是英语世界（英国及其殖民地）使用公历的第一个完整年份。这也是周一，通常被认为对于工作日周期来说是方便的。

    从儒略历（“旧式”）到公历（“新式”）的实际过渡发生在 1752 年，其中 10 月 2 日星期三之后是 10 月 14 日星期四。工作日的进程没有中断。

    从 1752 年开始，年份的开始也改为 1 月 1 日，而在 1751 年，年份开始于 3 月 25 日/26 日（3 月 25 日是女士日，是日历中传统的“四分之一日”之一，对于如何处理开始/结束有一些含糊之处）。这意味着 1751 年和 1752 年的年份长度都被打乱，不包含所有月份，而且 3 月并不是 1752 年之前的一年，而是跨越了两年（就像工作日周期往往跨越多年一样）。还有很多其他违规行为。

    1753 年实际上是日期可以落入的最早年份，并且算术不会有明显的额外复杂性。即使在 SQL Server 概念刚刚形成的 20 世纪 80 年代，它的出现也足以满足计算机化日期算术的几乎所有需求。

    DATETIME2 类型完全是“预推的”，因此尽管它能够表示追溯到公元 1 年 1 月 1 日的公历日期（如果没记错的话，是儒略历的公元 1 月 3 日），但该类型提供的实际日期算术对于现实世界的应用程序来说不太可能是正确的，除非古代日期已经重新转换为公历。

    罗马天主教会于 1582 年 9 月改用格里高利历，并能够动员罗马天主教世界中的大部分人这样做，因此在非英语国家中，当代格里高利历的日期可能可以追溯到当时。

    但那时英国已经脱离了教皇的权威，处于亨利八世的统治之下，因此直到170年后才实现转型。

    与此同时，俄罗斯直到1918年2月才发生转型，这就是为什么1917年的“十月革命”发生在公历11月。俄罗斯仍然有一个非常保守的教会，遵循儒略历，因此他们在现在公历的一月庆祝宗教圣诞节。

    • 3

  3. 允许的最低日期时间是 1753-01-01。因此，尝试使用 datetime2 ，如问题中的链接所示：

    --（正如其中一个答案和为什么你应该总是写“varchar”，其长度放在括号中？通常，如果不这样做，您会得到正确的结果 - DBA SE，varchar(length)而不是仅仅varchar）--

    选择 CONVERT(VARCHAR, DATEADD(DAY,-1,CAST('1753-01-01' AS DATETIME2)),100)

    SELECT CONVERT(VARCHAR(30), DATEADD(DAY,-1,CAST('1753-01-01' AS DATETIME2)),100)

    出去：

    Dec 31 1752 12:00AM

    和

    SELECT DATEDIFF(dd,'1753-01-01', '2003-12-24')

    给出：

    91667

    但这对我来说没什么意义了。因此，我从 0 获取 datediff，甚至无需将其转换为 datetime2，它仍然有效：

    SELECT DATEDIFF(dd,'1753-01-01', '0001-01-01')

    出去：

    -639905

    这是只需从每个整数中减去即可进行转换的数字：

    DATEADD(dd, 731573 - 639905 - 1, '17530101')

    使：

    2003-12-24 00:00:00.000

    由于我只记录了 20 世纪及以后的日期，所以我还可以采取另一个开头：

    SELECT DATEDIFF(dd,'1900-01-01', '0001-01-01')

    出去：

    -693595

    那么就会变成：

    SELECT DATEADD(dd, 731573 - 693595 - 1, '19000101')

    出去：

    2003-12-24 00:00:00.000

    然后我发现这表明日期时间的转换以 1900-01-01 作为内置开始：

    select cast(731573 - 693596 as datetime)

    出去：

    2003-12-24 00:00:00.000

    或者您可能需要：

    select convert(varchar(8), cast(731573 - 693596 as datetime), 112)

    出去：

    20031224

    现在要将其转换回整数，您需要：

    SELECT DATEDIFF(dd,'1900-01-01', '2003-12-24') + 693596
-- Or:
SELECT DATEDIFF(dd,'1900-01-01', '20031224') + 693596
-- Or:
SELECT DATEDIFF(dd,'19000101', '20031224') + 693596

    出去：

    731573

    或者从第一年开始：

    SELECT DATEDIFF(dd,'0001-01-01', '2003-12-24') + 1

    出去：

    731573

    731573因此，两者的输出都是问题示例中所需的整数2003-12-24。

    • 1

相关问题