用 SQL 思考 - 处理日期
4.58/5 (20投票s)
在本期中，我将讨论在 SQL Server 中处理日期和日期函数。
引言
我写这些文章的原因之一是，当我在论坛上看到常见问题需要深入解答时，我就可以从容应对。最近，我看到有人问“在 SQL Server 中存储日期时，应该使用 datetime 数据类型还是 varchar？”，这让我非常惊讶。当然，关于更改日期格式的问题常常表明用户已将日期存储为字符串。因此，今天我将通过一些练习来说明如何在 SQL Server 中最好地处理日期和时间，并在此过程中确定一些我们绝不应该做的事情。
获取日期
我想，您首先想知道的，就是日期，对吧？
select CURRENT_TIMESTAMP, getdate(), GETUTCDATE()
将以您的时区获取当前日期两次，然后 GETUTCDATE 将如您所料获取 UTC 日期。如果您像我一样年纪大了，UTC 与 GMT 相同。如果您年纪不大，那么 UTC 是已本地化以消除时区偏移量的时间。根据文档，CURRENT_TIMESTAMP 和 getdate() 在这方面有所不同（文档称 getdate() 使用时区，而没有说 CURRENT_TIMESTAMP 是的）。这是错误的。它们是同一回事。区别在于 CURRENT_TIMESTAMP 是标准的 SQL，因此您应该使用它（因为它应该在任何地方都适用）。getdate() 是 SQL Server 函数，不能保证在其他 SQL 平台上也能工作。
创建测试数据
首先，我们将创建一些数据。我们的表存储姓名和生日。它非常简单，但我将展示的所有概念都不会改变，即使表除了我们使用的列之外还有很多列。
create table tblMember
(
id int identity,
name varchar(100),
DOB varchar(50)
)
insert into tblMember values('Fred', '1969/02/17'), ('Bill', '1971/07/04'), ('Bob', '1973/09/15'), ('Sarah', 'The first Monday in 1982')
insert into tblMember values('Robert', '05/14/1995'), ('Hank', '13/05/2001'), ('Laura', 'Feb 17 1969'), ('Emma', 'Not telling')
您可能已经发现此数据中的一些问题。如果您必须处理此类数据，并且有一个包含几个错误值的大表，那么可以通过一些方法获取包含错误值的行的列表。以下是在 SS2012 中执行此操作的方法：
select id, name, dob from tblMember where try_parse(DOB as date) is null
但是这个版本在旧版本中也同样好用。
select id, name, dob from tblMember where isdate(DOB) = 0
区域设置问题
您在测试数据中可能注意到的一件事是，在所有其他条目之间，有一些条目明显是 yyyy/mm/dd 格式，而另一些条目明显是 yyyy/dd/mm 格式。实际上，人们更常将日期存储为 mm/dd/yyyy 或 dd/mm/yyyy，然后假设其系统的区域设置永远不会改变。唯一不受区域设置影响的日期格式是 **yyyymmdd**，没有斜杠。因此，如果您必须将日期存储为字符串，那么这是唯一合理的方式，除非您想处理使用文本表示月份的较长字符串（我承认这更易于人类阅读，但将其转换为以便更改字符串格式仍然是一个两步过程）。但是，同样，没有任何字符串格式比日期格式更清晰地表示日期。从我插入的其他条目中可以看出，如果您将日期存储为字符串，尤其是在收集日期为字符串时，那么存在各种人类可理解但对自动化环境无用的条目（例如，如果我们想在用户的生日那天向所有用户发送生日贺卡）。
显示日期
当然，通常希望以特定格式提取日期，这是日期时间类型的优势之一，它是纯粹的数据，但您可以将其转换为任何您喜欢的显示字符串格式。SQL Server 也可以在日期被用作字符串的替代时，尝试隐式地将字符串转换为日期，但这既慢，而且一旦发生任何错误，整个请求都会失败。尝试运行此命令以查看效果：
select CONVERT(nvarchar(30), DOB, 109) from tblMember
您可以尝试运行此命令：
select DOB, CONVERT(nvarchar(30), DOB, 109) from tblMember where isdate(DOB) = 1
它将返回原始日期字符串，而不会对其进行格式化。Convert 函数适用于所有可能的类型，因此即使有格式标识符，也不能完全清楚您想处理日期时间。您需要执行以下操作：
select DOB, CONVERT(nvarchar(30), convert(datetime,DOB), 109) from tblMember where isdate(DOB) = 1
我不确定为什么 SQL Server 在处理无效日期时进行转换会失败，但又未能识别出它需要处理日期，但无论如何，对我来说，将字符串转换为日期，然后将其转换为格式化字符串，这简直是疯狂的。
供您参考，此链接指向 Microsoft 关于可用于定义日期字符串格式的数字值的文档。
转换损坏的表
那么，如果您必须处理这样一个系统，您应该怎么做？我会先添加一个新列，并让所有插入和更新代码同时维护这两个值（可以使用触发器来实现）。然后，您可以随心所欲地转换所有 SELECT 语句，逐一测试，直到确定旧列不再使用，然后将其删除。
现在让我们添加该列，并将我们的值插入其中：
Alter Table tblMember Add Birthday date
现在我们可以尝试插入我们的值。
update tblMember set Birthday = DOB
如果像这样的语句可以正常运行，那么您的数据库中就没有损坏的行。否则，您需要执行此操作：
update tblMember set Birthday = try_parse(DOB as date)
或者，对于 SS2012 之前的版本：
update tblMember set Birthday = convert(date,DOB) where isdate(dob)
现在运行此 SQL：
select * from tblMember
您将看到类似这样的内容：
id name DOB Birthday 1 Fred 1969/02/17 1969-02-17 2 Bill 1971/07/04 1971-07-04 3 Bob 1973/09/15 1973-09-15 4 Sarah The first Monday in 1982 NULL 5 Robert 05/14/1995 1995-05-14 6 Hank 13/05/2001 NULL 7 Laura Feb 17 1969 1969-02-17 8 Emma Not telling NULL
请注意，现在我们的生日都在同一格式中，这使得它们更易于阅读。同时请注意，无意义的条目已消失。如果字段不是 NULL，我们就知道其格式，并且知道如何处理它。
处理日期的部分
通常，日期或日期时间只是一个整体数据块。但是，有时您想处理其部分，例如，当您从数据库中选择订单并按订单月份分组时。SQL Server 提供了一些函数来帮助处理此问题。有 day、month 和 year 函数，当您按这些内容分组时非常方便，但还有一个 DatePart 函数，它也可以返回上述所有值。
从 SQL Server 2008 开始，有了不带时间的日期和不带日期的日期类型，以及 datetime2，它比旧的 datetime 格式更符合标准。因此，应该优先使用这些类型，虽然我会提及它们，并且我们在表中使用了 date 而不是 datetime，但我有很多示例使用了 datetime，因为我确信在现实世界中您会经常遇到它。Datetime2 可以表示比 datetime 更大的日期范围，并且具有更大的默认精度级别（用户也可以更改）。
与往常一样，尝试处理字符串字段会出错。
select datepart(dw, dob) from tblMember
现在我们的函数知道我们需要转换为 datetime，这将正常工作：
select datepart(dw, dob) from tblMember where isdate(dob) = 1
如果我们有 SS2012，我们可以使用 try_parse 并获取 null 值（这意味着我们的 SELECT 无法完全跳过有效行，因为它有一个错误的值），但使用 datetime 类型会更简洁。
select datepart(dw, birthday) from tblMember
当然，从这里我们可以做更有趣的事情，例如计算某人活了多久：
select datediff(dd, birthday, getdate()) from tblMember
或者他们的年龄（这可能更有用）：
select datediff(yy, birthday, getdate()) from tblMember
我们甚至可以定义一个包含日期部分的自定义字符串：
select convert(char(4), year(birthday)) + '->' +
case when month(birthday) < 10 then '0' else '' end +
convert(char(2), month(birthday)) + '->' +
case when month(birthday) < 10 then '0' else '' end +
convert(char(2), day(birthday)) from tblMember
最后一个在 SS2012 中绝对更容易。
select format(birthday, 'YYYY->MM->dd') from tblMember
使用字符串的更多问题
让我们改变一下话题，谈谈如果将日期存储为字符串会破坏的其他内容。编写指定日期为字符串的 SQL 是非常常见的。如果您的日期是字符串，那么这意味着您需要匹配格式。尝试运行此命令：
select * from tblMember where dob = 'Feb 17 1969'
与此结果进行比较：
select * from tblMember where birthday = 'Feb 17 1969'
因为我们的日期时间字段正确地关心底层数据，而不是格式，所以这将正常工作。
如果我们想对结果进行排序，情况也是如此。尝试运行此命令：
select * from tblMember order by dob desc
由于该字段是字符串，因此它已按文本顺序排序。但是，这可能是我们想要的顺序：
select * from tblMember order by birthday desc
当然，我们可以将其转换为 datetime，但是，如果我们知道我们想要 datetime，为什么一开始不将其存储为 datetime 呢？
转换/比较日期
一个常见的情况是，旧数据库使用 datetime 来存储日期，然后想通过比较来获取今天的日期或另一个固定日期的行。网上有很多用于去除日期时间时间部分的 SQL 代码，大多数都很复杂且难以记忆。最有效的方法其实非常容易记住：
declare @now datetime = getdate()
declare @nowDate date = convert(date, @now)
declare @nowTime time = convert(time, @now)
select @now, @nowDate, @nowTime
将日期时间转换为日期会简单地去掉时间。将日期或时间转换为 datetime，会用默认值填充额外信息，这是确定默认日期是什么的好方法（毫不奇怪，默认时间是午夜）。
declare @now datetime = getdate()
declare @nowTime time = convert(time, @now)
set @now = convert(datetime, @nowTime)
select @now
默认日期是 1901 年 1 月 1 日。这将在下一节中变得很重要。
比较日期和时间
为了进行一些比较，我们首先需要创建一些值，如下所示：
declare @now datetime = getdate()
declare @then datetime = dateadd(dd, 45, @now)
set @then = dateadd(minute, 164, @then)
select @now, @then
declare @nowDate date = convert(date, @now)
declare @nowTime time = convert(time, @now)
declare @thenDate date = convert(date, @then)
declare @thenTime time = convert(time, @then)
这将创建今天的日期和时间，以及从现在起 45 天零 164 分钟后的日期和时间。
select @then + @now
select @then - @now
这将返回以下内容：
2128-06-26 03:34:53.367
1900-02-15 02:44:00.000
1900 年 1 月 1 日是基线，它是“零”，因此加减值之类的内容可以工作，但它给出的内容并不特别有用。
select convert(time, (@then - @now))
returns
02:44:00.0000000
也就是说，两个值之间的时间差，不考虑日期差异。
select @thenDate + @nowDate
select @thenTime + @nowTime
select @thenDate - @nowDate
select @thenTime - @nowTime
所有这些都会产生错误。我怀疑允许在 datetime 上进行加减操作是未经深思熟虑的，而且因为它并没有什么特别有用的地方，所以它已被从较新的 DATE 或 TIME 类型中移除。比较日期的正确方法是 DATEDIFF 函数。在此处有完整的文档：datediff。
简而言之，datediff 函数接受两个日期和要比较的部分。这是可能值的列表。注意：完整名称和缩写都是有效的。
| datepart | 缩写 |
| year | yy, yyyy |
| quarter | qq, q |
| month | mm, m |
| dayofyear | dy, y |
| day | dd, d |
| 周 | wk, ww |
| weekday | dw, w |
| hour | hh |
| minute | mi, n |
| second | ss, s |
| millisecond | ms |
| microsecond | mcs |
| nanosecond | ns |
请注意，DATEDIFF 始终将星期日视为一周的第一天（尽管稍后我将展示如何更改一周的第一天，用于其他操作）。
select datediff(dd, @nowdate, @thendate)
select datediff(mm, @nowdate, @thendate)
-- this also works with datetimes
select datediff(day, @now, @then)
select datediff(mm, @now, @then)
-- A combination of dates and datetimes is fine
select datediff(dd, @now, @thenDate)
select datediff(mm, @nowDate, @then)
select datediff(minute, @nowTime, @thenTime)
所有这些都返回相同的值。dd 检查返回 45，月份检查返回 mm。分钟检查返回 164，因此它是总分钟数，而不是忽略其他较大值的分钟数。
select datediff(minute, @now, @thenTime)
这将返回 -59988960。这是因为当我们传入时间时，会假定日期为 1900 年 1 月 1 日并将其计算在内。当然，如上所示，传入两个时间是可以的（因为两者假定的日期相同）。
select datediff(minute, @nowDate, @thenDate)
select datediff(minute, @now, @thenDate)
仅使用日期并要求基于时间的差异会产生自身的问题，因为它假定时间为午夜。对我来说，现在的值是 64800 和 64037。两者之间的差异是实际时间（午餐时间）与午夜（@nowDate 中的时间）之间的差异。
值得注意的是，我正在讨论 SQL Server 中的许多不同类型，DATE 和 TIME 都于 SS 2008 年推出，DATETIME2 和 DATETIMEOFFSET 也是如此。因此，如果您使用的是 SS2005，您的选择将是有限的。
SS2012 中的新日期函数
SS2012 中有两个日期相关的新方法集群。第二个集群是一个函数，但所有其他函数基本上都是相同的。它们的形式为 XXXFromParts，可以通过表示各部分的参数来创建每种可能的日期或时间类型。它们如下：
DATEFROMPARTS(2012, 12, 1)
DATETIMEFROMPARTS(2012, 12, 1, 10, 15, 30, 000)
-- 最后一个参数是毫秒部分中有多少个有效数字 DATETIME2FROMPARTS(100, 12, 1, 10, 15, 30, 279, 5)
SMALLDATETIMEFROMPARTS(1900, 12, 1, 10, 15)
-- 再次，毫秒具有可变数量的字符 TIMEFROMPARTS(12,30,15,1, 2)
-- 最后三个参数是小时和分钟的偏移量以及精度 SELECT DATETIMEOFFSETFROMPARTS ( 2010, 12, 31, 14, 23, 23, 0, 12, 0, 7 )
月底
另一个新函数是 EOMONTH，它计算月份的最后一天。EOMONTH(date) 返回该月的最后一天。EOMONTH(date, offset) 返回从传入月份开始或结束的“offset”个月后的月份的最后一天。
设置一周的第一天
datepart 'dw' 返回一周中的某一天。英语的默认值是星期日。如果您编写的任何代码用于任何目的（DATEDIFF 除外，它会忽略此设置），则应捕获当前值，将其设置为您期望的值，并在完成后重置它。您可以使用 @@DATEFIRST 来获取当前一周的第一天，例如：
SELECT @@DATEFIRST
或
declare @firstDay int
set @firstDay = @@DATEFIRST
您可以这样设置一周的第一天：
-- Make Monday the first day of the week.
SET DATEFIRST 1;
结论
SQL 和 TSQL 对日期、时间和带时间的日期都有丰富的支持。如果您处理日期，则应始终将它们存储为日期。除非时间信息相关，否则不应存储时间信息，有时存储它可能会导致错误（例如，如果您使用 <= 和 >= 检查两个日期之间的范围，那么具有默认值为午夜的时间部分可能会导致最后一个日期被忽略，并且如果值包含时间部分，则应避免使用 BETWEEN 运算符）。
将日期存储为字符串始终是尽可能糟糕的选择，任何您继承的执行此操作的数据库，都应将修复此问题作为一项重要任务，方法是添加一个使用日期类型的新列，使用触发器保持其最新，并随着时间的推移淘汰旧列。
如果可以，请始终使用标准 SQL，并在使用专有 SQL 时加以注意，因为您永远不知道您的老板或客户可能何时要求您在另一个 SQL 平台上工作。
虽然您的代码可能始终通过做出大多数时间都有效的假设来运行，但那些晦涩的 bug 最难追踪，因为它们往往是间歇性的，因此您的代码应该尽可能健壮。
我并没有真正涵盖让 SQL Server 将日期格式化为字符串的方法，因为我认为最好将数据委托给数据层，并将格式化委托给表示层。让 SQL Server 格式化和转换您的数据，是一项应转交给表示层的负载。