在SQL Server中存储旧日期的最佳方法

Question

什么是在SQL Server 2005中存储旧日期（1753年以前）的最佳/最有效方法？我只关心日期，不涉及时间。SQL Server的datetime数据类型只能保存自1753年1月1日以来的日期。MSDN文档说明有日期和datetime2数据类型，但SQL Server Management Studio似乎不支持它们（错误：无效的数据类型）。

将日期存储为“YYYYMMDD”格式的字符串或整数形式会导致多大的效率低下？我的表中有两个日期字段（StartDate和EndDate），需要进行大量的查询和排序。

更新：

下面有一些建议将年、月和日存储在不同的字段中。将部分内容存储在不同的字段中的好处是什么，而不是在单个整数字段中存储？

- Rick

---

使用3个整数而不是1个的优点在于，您无需像CodeMonkey1建议的那样计算天数。这比听起来更困难。 - Treb

这些日期是关于什么的？你可能需要考虑到公历和儒略历问题。 - Martin Smith

Answer 1

日期类型绝对是您想要使用的。它的范围是“公元1年1月1日至公元9999年12月31日”。它仅存储日期信息，不包括时间部分。

也许您正在使用的是SSMS 2005而不是2008，或者连接到了一个2005实例？该类型是在SQL Server 2008中引入的。如果您有使用2008数据库的能力，我认为无疑应该使用它。

- bdukes

---

听起来像个升级的理由！ - Ian G

Answer 2

我从未这样做过，但也许您可以将日期存储为表示自您所选择的最小日期以来的天数的整数。然后，您可以创建一个查找表，将这些整数映射到年、月和日，或者编写用户定义的函数将整数转换为日期或反之亦然。这在选择和排序方面应该相当高效。

- CodeMonkey1

Answer 3

把年、月、日存储为整数，比使用字符串更高效。查询时需要输入更多语句，但可以通过索引使查询速度更快，并且能够让索引的方式符合你正在进行的查询类型。

例如：

CREATE TABLE myOldDates (
  year INT,
  month INT,
  day INT,
  -- otherstuff ...
)

然后所有查询都会像这样：

-- get records between 5/15/1752 and 3/19/1754
SELECT * FROM myOldDates
  WHERE 
    (year = 1752 AND ((month = 5 and day >= 15) or month > 5) OR year > 1752)
    AND (year = 1754 AND ((month = 3 and day <= 19) or month < 3) OR year < 1754)

确实很丑陋，但这就是范围查询的极限了，一旦你第一次写好它，就可以将其封装在一个函数中。

- Ian Varley

Answer 4

使用YYYYMMDD格式存储日期的一个问题是可能会出现不存在的日期（例如16000231-2月31日不存在）。在将其输入数据库之前，您需要在客户端进行一些验证。

对于以年、月和日整数形式存储日期，正如Ian Varley所建议的那样，同样存在这个问题。但除此之外，我喜欢他的答案，只希望我能想到它;-)

- Treb

---

每个合适的客户端都会为其从任何外部来源接收到的数据执行什么操作？ :)（提示：验证它） - Esko

是的。这只是在处理日期时可以忽略的事情，因为内置的日期类型会为您进行验证。 - Treb

Answer 5

YYYYMMDD = 8个字节。您可以使用3列的SMALLINT和TINYINT将其缩减为4个字节。

- Joshua

Answer 6

像CodeMonkey1建议的那样使用整数似乎是个好主意，这将使“日期计算”更容易（例如，某个日期+XX天）。

编写一些UDF（也如CodeMonkey1建议的那样），将int --> YYYYMMDD --> int进行转换，您将拥有Ian Varley在他的答案中提到的灵活性。

- Alan

Answer 7

一个想法——如果你有一些.NET知识，你可以创建一个CLR类型来存储日期，这实际上将是一个datetime。如果你要对日期进行很多计算而不仅仅是简单的查询，那么这可能是值得调查的东西。

- Conrad

Answer 8

将日期存储为早至公元前1/1/4713年（一直到现代）的一种方法是使用儒略日。这与儒略日期不同，它是一个整数，编码自公元前1/1/4713年以来的天数。

为了简单起见，以下是从日期到儒略日和相反方向的转换。请注意，如果超出DATE类型的范围，则仍然无法将儒略日转换为日期，但正如bdukes在上面所说，DATE应该追溯到1/1/0001。

IF OBJECT_ID (N'dbo.ufn_JulianDayFromDate', N'FN') IS NULL
    exec('CREATE function [dbo].[ufn_JulianDayFromDate] () returns int As begin 
return 1 end;');
go

alter function dbo.ufn_JulianDayFromDate(@theDate as date) returns int
as 
begin
    declare @JulianDayBase int=693596;
    return @JulianDayBase + datediff(d, 0, @theDate);
end;
go

并且

IF OBJECT_ID (N'dbo.ufn_DateFromJulianDay', N'FN') IS NULL
    exec('CREATE function [dbo].[ufn_DateFromJulianDay] () returns int As begin return 1 end;');
go

alter function dbo.ufn_DateFromJulianDay(@JulianDay as int) returns date
as 
begin
    declare @JulianDayBase int=693596;
    return dateadd(d, @JulianDay-@JulianDayBase, '1/1/1900')
end;

go

- RBerman