Robyn Page 的 SQL Server 日期/时间工作台
0/5 (0投票)
2007年1月8日
7分钟阅读
65039
143
诸如获取“月初”的 DATETIME 之类的看似简单的要求，可能会非常棘手。
这是我们 CodeProject 赞助商的产品展示评测。这些评测旨在为您提供我们认为对开发人员有用且有价值的产品和服务的信息。
引言
我认为，我并非少数在学习编程技能时最好通过实践来学习的人！因此，这个关于 SQL Server 中日期和时间用法的“工作台”与 Simple-Talk 上“ usual”的文章有所不同。它被设计成可以完整地粘贴到查询分析器、SSMS 或其他 GUI 中，并执行单个示例。
这个关于 SQL 中日期和时间用法的“工作台”结构清晰，可以完整地粘贴到查询分析器、SSMS 或其他 GUI 中，并执行各个示例。我认为，我并非少数在学习编程技能时最好通过实践来学习的人！
它提供了解决所有常见日期相关需求和问题的可用示例，包括
- 输入和输出日期
- 使用 ISDATE 检查有效日期
- 提取日期的一部分
- 查找两个日期之间的差异
- 格式化日期
- 计算日期（例如，月初）
- 实际日期用法（例如，计算每日总计）
它还解决了更棘手的问题，例如将 UNIX 时间戳转换为 DateTime 以及查找一周开始时间的 DATETIME 。
我鼓励您进行实验。您总会发现惊喜；例如，在我写这篇文章之前，我从未考虑过在搜索 DateTime 字段时使用 'LIKE' ，或者在存储过程中使用 { t '2:40'} 作为文字日期。
同样，我总是喜欢在任何关于 SQL Server 的文章中看到尽可能多的示例。没有什么比这更能激发灵感了。正式的描述对那些大脑里有奇怪肿块的人来说很好，但我更希望看到清晰的解释，并穿插示例！
如果我有什么普遍的建议，那就是要利用 DATETIME 数据类型的优势，并且永远不要试图绕过它的使用，而是以其他任何格式存储日期或时间。我从未遇到过这种情况能提供任何持久益处。
目录
- 输入日期
- 输入时间
- 输出日期
- 操作日期
- 格式化日期
- 计算日期
- 日期转换
- 使用日期
输入日期
用户在表单中输入日期，您需要将其插入数据库中的 DATETIME 数据类型。日期可以作为字符串分配给 DateTime 变量或列，但这取决于特定当前语言存储的日期格式。月份 (m)、日期 (d) 和年份 (y) 的顺序在不同国家/地区是不同的。US_English (mdy) 与 British (dmy) 不同。通过显式设置日期格式，您可以覆盖此设置。
您可以通过以下方式查看当前的 DateFormat 以及其他内容...
DBCC USEROPTIONS
现在，为了演示错误地处理这个问题可能导致意外错误.....
SET language british SELECT CAST('14/2/2006' AS datetime)
--2006-02-14 00:00:00.000
SET language us_english --Changed language setting to us_english.
SELECT CAST('14/2/2006' AS datetime) --**ERROR!***
--keep speaking American, but use the european date format
SET dateformat 'dmy' --to override the language default
SELECT CAST('14/2/2006' AS datetime) --2006-02-14 00:00:00.000
SET language british SELECT CAST('14/2/2006' AS datetime)
--2006-02-14 00:00:00.000
SET language us_english --Changed language setting to us_english.
SELECT CAST('14/2/2006' AS datetime) --2006-02-14 00:00:00.000
因此，任何基于单词的日期表示（例如，febbraio、fevereiro、february）在任何使用不同月份单词的语言中都会失败。要查看当前的语言设置，请使用
sp_HelpLanguage
要导入外语日期，您必须更改连接的语言设置。例如：
SET language Italiano SELECT CAST('10 febbraio 2006' AS datetime)
--Changed language setting to Italiano.
--2006-02-10 00:00:00.000
--to see the current language settings, use
/* Otherwise SQL Server is fairly accomodating, and will do its best to make
sense of a date.*/
--all of the following return 2006-02-01 00:00:00.000
SET language british
SELECT CAST('1 feb 2006' AS datetime)--remember, this is language dependent
SELECT CAST('1 february 2006' AS datetime)--this too
SELECT CAST('01-02-06' AS datetime)
SELECT CAST('2006-02-01 00:00:00.000' AS datetime)
SELECT CAST('1/2/06' AS datetime)
SELECT CAST('1.2.06' AS datetime)
SELECT CAST('20060201' AS datetime)
--in SQL Server 2000 and 2005 you can specify dates in ISO 8601 format
SELECT CAST('2006-02-01T00:00:00' AS datetime)
SELECT CAST('2006-02-01T00:00:00.000' AS datetime)
--and you'll be able to enter in this format whatever the settings!
ANSI 标准日期使用花括号，标记 'd' 表示日期，以及日期字符串
SELECT { d '2006-02-01' }
ANSI 标准日期时间使用 'ts' 代替 'd'，并添加小时、分钟和秒到日期（使用 24 小时制）
SELECT { ts '2006-02-01 00:00:00' }
如果您使用 CONVERT 函数，则可以通过选择正确的 CONVERT 样式来覆盖日期格式（103 是英式/法式格式 dd/mm/yyyy（稍后将列出所有样式）
SET language us_english
SELECT CONVERT(DateTime,'25/2/2006',103) --works fine
--whereas the 100 style uses the default supplied by the dateformat.
SELECT CONVERT(DateTime,'25/2/2006',100) --error!
IsDate 函数
IsDate(expression) 函数用于检查字符串是否为有效日期。它依赖于语言。ISDATE (Expression) 如果表达式是有效日期（根据语言和日期格式掩码），则返回 1；如果不是，则返回 0。
以下演示使用 ISDATE 来测试将字符串作为日期输入。
SET LANGUAGE british SET nocount ON
DECLARE @DateAsString VARCHAR(20),
@DateAsDateTime DateTime
SELECT @DateAsString='2 february 2002'
SELECT [input]=@DateAsString
IF (ISDATE(@DateAsString)=1)
BEGIN
SELECT @DateAsDateTime=@DateAsString
SELECT [the Date]=COALESCE(CONVERT(CHAR(17),@DateAsDateTime,113),
'unrecognised')
END
ELSE
SELECT [the Date] ='That was not a date'
输入时间
时间也可以轻松地输入到 SQL Server 中。没有单独的时间和日期数据类型仅用于存储时间或仅存储日期。没有必要。如果仅在设置 datetime 时指定时间，则日期假定为 1900 年 1 月 1 日，新千年的开始年份。
如果仅指定日期，则时间默认为午夜。
例如：
SELECT CAST ('17:45' AS datetime) -- 1900-01-01 17:45:00.000
SELECT CAST ('13:20:25:850' AS datetime) -- 1900-01-01 13:20:25.850
SELECT CAST ('14:30:20.9' AS datetime) -- 1900-01-01 14:30:20.900
SELECT CAST ('3am' AS datetime) -- 1900-01-01 03:00:00.000
SELECT CAST ('10 PM' AS datetime) -- 1900-01-01 22:00:00.000
SELECT CAST ('02:50:20:500AM' AS datetime) -- 1900-01-01 02:50:20.500
SELECT CONVERT (DateTime,'02:50:20',108) -- 1900-01-01 02:50:20.000
--And times can be converted back from the DATETIME into the ascii VARCHAR
--version as follows...
SELECT CONVERT(VARCHAR(20),GETDATE(),108) -- 15:08:52
--108 is the hh:mm:ss CONVERT style (See next section for the complete list)
SELECT LTRIM(RIGHT(CONVERT(CHAR(19),GETDATE(),100),7))-- 3:10PM
SELECT LTRIM(RIGHT(CONVERT(CHAR(26),GETDATE(),109),14)) -- 3:19:18:810PM
等等。
您可以通过其他方式输入时间（请注意，括号是花括号）
SELECT { t '09:40:00' }
这会意外地得到今天的 09.40，而不是 1900 年 1 月 1 日的 9:40！（正如您可能从其他时间输入示例中预期的那样）
--this is valid in a stored procedure too
CREATE PROCEDURE #spExperiment AS
SELECT { t '09:40:00' }
GO
EXEC #spExperiment
输出日期
可以使用 CONVERT 函数和 CONVERT 样式以多种方式将日期输出为字符串。这些样式是数字代码，对应于最流行的日期格式。与 CAST 函数相比，CONVERT 函数提供了更大的灵活性。
CONVERT 样式会覆盖 DATEFORMAT 的设置，但在日期格式使用月份名称时会使用当前的语言设置。
如果您运行以下代码，您将获得一个说明所有内置格式的结果，使用当前的日期和时间。
DECLARE @types TABLE(
[2 digit year] INT NULL,
[4 digit year] INT NOT NULL,
name VARCHAR(40))
SET LANGUAGE british SET nocount ON
--Each select statement is followed by an example of a string that uses the
--style
INSERT INTO @types
SELECT NULL,100,'Default'--Oct 17 2006 9:29PM
INSERT INTO @types
SELECT 1,101, 'USA'--10/17/06 or 10/17/2006
INSERT INTO @types
SELECT 2,102, 'ANSI'--06.10.17 or 2006.10.17
INSERT INTO @types
SELECT 3,103, 'British/French'--17/10/06 or 17/10/2006
INSERT INTO @types
SELECT 4,104, 'German'--17.10.06 or 17.10.2006
INSERT INTO @types
SELECT 5,105, 'Italian'--17-10-06 or 17-10-2006
INSERT INTO @types
SELECT 6,106, 'dd mon yy'--17 Oct 06 or 17 Oct 2006
INSERT INTO @types
SELECT 7,107, 'Mon dd, yy'--Oct 17, 06 or Oct 17, 2006
INSERT INTO @types
SELECT 8,108, 'hh:mm:ss' --21:29:45 or 21:29:45
INSERT INTO @types
SELECT NULL,109, 'Default + milliseconds'--Oct 17 2006 9:29:45:500PM
INSERT INTO @types
SELECT 10,110,'USA' --10-17-06 or 10-17-2006
INSERT INTO @types
SELECT 11,111,'JAPAN'--06/10/17 or 2006/10/17
INSERT INTO @types
SELECT 12,112,'ISO'--061017 or 20061017
INSERT INTO @types
SELECT NULL,113,'Europe default(24h) + milliseconds'--17 Oct 2006 21:29:45:500
INSERT INTO @types
SELECT 14,114,'hh:mi:ss:mmm (24h)' --21:29:45:500 or 21:29:45:500
INSERT INTO @types
SELECT NULL,120,'ODBC canonical (24h)'--2006-10-17 21:29:45
INSERT INTO @types
SELECT NULL,121, 'ODBC canonical (24h)+ milliseconds'
--2006-10-17 21:29:45.500
INSERT INTO @types
SELECT NULL,126, 'ISO8601'--2006-10-17T21:29:45.500
--insert into @types Select null,127, 'ISO8601 with time zone'
--SQL Server 2005 only!
INSERT INTO @types
SELECT NULL,130, 'Hijri'--25 ????? 1427 9:33:21:340PM
INSERT INTO @types
SELECT NULL,131, 'Hijri'--25/09/1427 9:29:45:500PM
SELECT [name], [2 digit year]=COALESCE(CONVERT(VARCHAR(3),[2 digit year]),'-'),
[example]=CASE WHEN [2 digit year] IS NOT NULL
THEN CONVERT(VARCHAR(30),GETDATE(),[2 digit year])
ELSE '-' END,
[4 digit year]=COALESCE(CONVERT(VARCHAR(3),[4 digit year]),'-'),
[example]=CASE WHEN [4 digit year] IS NOT NULL
THEN CONVERT(VARCHAR(30),GETDATE(),[4 digit year])
ELSE '-' END
FROM @types
操作日期
可以通过三个函数获取当前日期
SELECT GETDATE() --the local date and time
SELECT GETUTCDATE() --the UTC or GMT date and time
ELECT CURRENT_TIMESTAMP--synonymous with GetDate()
在提取 DateTime 的部分时，您可以使用一些返回整数的实用函数：DAY、MONTH、YEAR 。这里我们以整数形式获取日期、月份和年份。
SELECT DAY(GETDATE()),MONTH(GETDATE()),YEAR(GETDATE())
DAY MONTH AND YEAR 函数是等效 DATEPART 命令的简写，但对于更通用的用途，DATEPART 函数更灵活。
SELECT DATEPART(DAY,GETDATE()),DATEPART(MONTH,GETDATE()),
DATEPART(YEAR,GETDATE())
DATEADD
DATEADD 实际上可以向您指定的日期添加一定数量的年份、季度、月份、周、天、小时、分钟、秒或毫秒。
- 年 (yy 或 yyyy)
- 季度 (qq 或 q)
- 月 (mm 或 m)
- 周 (wk 或 ww)
- 日 (dayofyear, dy, y, day, dd, d, weekday 或 dw)
- 小时 (hh)
- 分钟 (mi 或 n)
- 秒 (ss 或 s)
- 毫秒 (ms)
在这些示例中，我们将日期与 DATEADDed 日期进行比较，以便您可以看到 DATEADD 对其产生的影响。
SELECT '2007-01-01 00:00:00.000', DATEADD(YEAR,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00.000', DATEADD(quarteer,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00.000', DATEADD(MONTH,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00.000',
DATEADD(dayofyear,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00.000', DATEADD(DAY,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00.000', DATEADD(week,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00.000', DATEADD(weekday,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00.000', DATEADD(hour,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00.000', DATEADD(minute,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00.000',
DATEADD(second ,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00.000',
DATEADD(millisecond,100,'2007-01-01 00:00:00.000')
可以通过三个函数获取当前日期。
SELECT GETDATE() --the loval date and time
SELECT GETUTCDATE() --the UTC or GMT date and time
SELECT CURRENT_TIMESTAMP--synonymous with GetDate()
DATEDIFF
DATEDIFF 返回两个日期之间差异的整数，以年、季度、月、周、天、小时、分钟、秒或毫秒表示（它计算边界）。
SELECT DATEDIFF(DAY,'1 feb 2006','1 mar 2006')--28
SELECT DATEDIFF(DAY,'1 feb 2008','1 mar 2008')--29. Hmm must be a leap year!
我们将在工作坊稍后提供一些实际使用示例。
DATENAME
与返回整数的 DatePart 不同，DATENAME 返回一个 NVarchar，表示日期中的年份、季度、月份、周、星期几、一年中的第几天、小时、分钟、秒或毫秒。月份和星期几是从 sysLanguages 表中的值中给出的全称。
SELECT DATENAME (YEAR,GETDATE()) --2006
SELECT DATENAME (quarter,GETDATE()) --4
SELECT DATENAME (MONTH,GETDATE()) --October
SELECT DATENAME (dayofyear,GETDATE()) --285
SELECT DATENAME (DAY,GETDATE()) --12
SELECT DATENAME (week,GETDATE()) --42
SELECT DATENAME (weekday,GETDATE()) --Thursday
SELECT DATENAME (hour,GETDATE()) --9
SELECT DATENAME (minute,GETDATE()) --32
SELECT DATENAME (second ,GETDATE()) --8
SELECT DATENAME (millisecond,GETDATE()) --875
DATEPART
DATEPART 返回一个整数，表示第一个参数请求的日期部分。您可以使用年 (yy 或 yyyy)、季度 (qq 或 q)、月 (mm 或 m)、一年中的第几天 (dy 或 y)、日 (dd 或 d)、周 (wk 或 ww)、星期几 (dw)、小时 (hh)、分钟 (mi 或 n)、秒 (ss 或 s) 或毫秒 (ms)。
SELECT DATEPART(YEAR,GETDATE()) --2006
SELECT DATEPART(quarter,GETDATE()) --4
SELECT DATEPART(MONTH,GETDATE()) --10
SELECT DATEPART(dayofyear,GETDATE()) --285
SELECT DATEPART(DAY,GETDATE()) --12
SELECT DATEPART(week,GETDATE()) --42
SELECT DATEPART(weekday,GETDATE()) --4
SELECT DATEPART(hour,GETDATE()) --9
SELECT DATEPART(minute,GETDATE()) --32
SELECT DATEPART(second ,GETDATE()) --8
SELECT DATEPART(millisecond,GETDATE()) --875
格式化日期
计算和格式化日期的示例
--To get the full Weekday name
SELECT DATENAME(dw,GETDATE())
--To get the abbreviated Weekday name (MON, TUE, WED etc)
SELECT LEFT(DATENAME(dw,GETDATE()),3)
--ISO-8601 Weekday number
SELECT DATEPART(dw,GETDATE())+(((@@Datefirst+3)%7)-4)
--Day of the month with leading zeros
SELECT RIGHT('00' + CAST(DAY(GETDATE()) AS VARCHAR),2)
--Day of the month without leading space
SELECT CAST(DAY(GETDATE()) AS VARCHAR)
--day of the year
SELECT DATEPART(dy,GETDATE())
--number of the week in the year
SELECT DATEPART(week,GETDATE())
--ISO-8601 number of the week of the year (monday as the first day of the week)
SET datefirst 1 SELECT DATEPART(week,GETDATE())
--you may need to preserve and restore the value
--full name of the month
SELECT DATENAME(MONTH,GETDATE())
--Abbreviated name of the month
SELECT LEFT(DATENAME(MONTH,GETDATE()),3)--not true of finnish or french!
--Number of the month with leading zeros
SELECT RIGHT('00' + CAST(MONTH(GETDATE()) AS VARCHAR),2)
--two-digit year
SELECT RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR),2)
--four-digit year
SELECT CAST(YEAR(GETDATE()) AS VARCHAR)
--hour (00-23)
SELECT DATEPART(hour,GETDATE())
--Hour (01-12)
SELECT LEFT(RIGHT(CONVERT(CHAR(19),GETDATE(),100),7),2)
--minute
SELECT DATEPART(minute,GETDATE())
--second
SELECT DATEPART(second,GETDATE())
--PM/AM indicator
SELECT RIGHT(CONVERT(CHAR(19),GETDATE(),100),2)
--time in 24 hour notation
SELECT CONVERT(VARCHAR(8),GETDATE(),8)
--Time in 12 hour notation
SELECT RIGHT(CONVERT(CHAR(19),GETDATE(),100),7)
--timezone (or daylight-saving)
SELECT DATEDIFF(hour, GETDATE(), GETUTCDATE())
----ordinal suffix for the date
SELECT SUBSTRING('stndrdthththththththththththththththththstndrdthththththththst'
,(DATEPART(DAY,GETDATE())*2)-1,2)
--full date (the variations are infinite. Here is one example
SELECT DATENAME(dw,GETDATE())+', '+ STUFF(CONVERT(CHAR(11),GETDATE(),106),3,0,
SUBSTRING('stndrdthththththththththththththththththstndrdthththththththst'
,(DATEPART(DAY,GETDATE())*2)-1,2))
--e.g. Thursday, 12th Oct 2006
按示例计算日期
-- now
SELECT GETDATE()
-- Start of today (first thing)
SELECT CAST(CONVERT(CHAR(11),GETDATE(),113) AS datetime)
-- Start of tomorrow (first thing)
SELECT CAST(CONVERT(CHAR(11),DATEADD(DAY,1,GETDATE()),113) AS datetime)
-- Start of yesterday (first thing)
SELECT CAST(CONVERT(CHAR(11),DATEADD(DAY,-1,GETDATE()),113) AS datetime)
-- This time Next thursday (today if it is thursday)
SELECT DATEADD(DAY,((7-DATEPART(dw,GETDATE())+(((@@Datefirst+3)%7)+2)) % 7),
GETDATE())
-- This time Last friday (today if it is friday)
SELECT DATEADD(DAY,-((7-DATEPART(dw,GETDATE())+(((@@Datefirst+3)%7)+3)) % 7),
GETDATE())
-- Two hours time
SELECT DATEADD(hour,2,GETDATE())
-- Two hours ago
SELECT DATEADD(hour,-2,GETDATE())
-- Same date and time last month
SELECT DATEADD(MONTH,-1,GETDATE())
-- Start of the month
SELECT CAST('01 '+ RIGHT(CONVERT(CHAR(11),GETDATE(),113),8) AS datetime)
-- Start of last month
SELECT CAST('01 ' + RIGHT(CONVERT(CHAR(11),DATEADD(MONTH,-1,GETDATE()),113),8)
AS datetime)
-- Start of next month
SELECT CAST('01 '+ RIGHT(CONVERT(CHAR(11),DATEADD(MONTH,1,GETDATE()),113),8)
AS datetime)
-- Ten minutes ago
SELECT DATEADD(minute,-10,GETDATE())
-- Midnight last night
SELECT CAST(CONVERT(CHAR(11),GETDATE(),113) AS datetime)
-- Midnight tonight
SELECT CAST(CONVERT(CHAR(11),DATEADD(DAY,1,GETDATE()),113) AS datetime)
-- Three weeks ago
SELECT DATEADD(week,-3,GETDATE())
-- Start of the week (this depends on your @@DateFirst setting)
SELECT DATEADD(DAY, -(DATEPART(dw,GETDATE())-1),GETDATE())
-- last year
SELECT DATEADD(YEAR,-1,GETDATE())
-- new year, this year
SELECT CAST('01 Jan'+ DATENAME(YEAR,GETDATE()) AS datetime)
-- new year, last year
SELECT CAST('01 Jan'+ DATENAME(YEAR,DATEADD(YEAR,-1,GETDATE())) AS datetime)
-- next christmas
SELECT CASE WHEN DATEPART(dy,GETDATE()) <
DATEPART(dy,'25 Dec' + DATENAME(YEAR,GETDATE()))
THEN CAST('25 Dec'+ + DATENAME(YEAR,GETDATE()) AS datetime)
ELSE CAST('25 Dec'+ CAST(DATEPART(YEAR,GETDATE())+1 AS VARCHAR) AS datetime)
END
日期转换
DATETIME 数据类型存储从 1753 年 1 月 1 日到 9999 年 12 月 31 日的日期和时间数据，精度为 3.33 毫秒。值会被四舍五入。
值存储为两个 4 字节整数。
- 第一个 4 字节存储自基准日期 1900 年 1 月 1 日以来的天数（+/-）。基准日期是系统参考日期。
- 第二个 4 字节存储一天的时间，表示为午夜后的毫秒数。
不允许使用早于 1753 年 1 月 1 日的 datetime 值。
从 SQL Server 日期转换为 Unix 时间戳时，日期将四舍五入到最近的秒（Unix 时间戳仅精确到秒）。SQL Server 日期到 UNIX 时间戳（基于自标准纪元 1970 年 1 月 1 日以来的秒数）
SELECT DATEDIFF(second,'1/1/1970',GETDATE())
-- UNIX timestamp to SQL Server
SELECT DATEADD(second, 1160986544, '1/1/1970')
使用日期
存储日期时，请始终使用 datetime 数据类型。不要试图使用诸如将年份、月份或日期作为整数存储之类的技巧，认为这会有助于检索和汇总报表。这永远不会奏效。datetime 的操作对 SQL Server 的性能至关重要，因为它经过高度优化。基于 DateTimes 的索引工作得非常好，排序正确，并且允许按各种条件（如周、月、年初至今等）进行快速分区。例如，如果您在像 PURCHASES 这样的表中按日期存储购买列表，您可以通过以下方式找到上周的总和...
SELECT HERE purchaseDate LIKE '%9:40%'
--or all purchases in the month of february
SELECT COUNT(*) FROM [purchases]
WHERE purchaseDate LIKE '%feb%'
--all purchases where there is a 'Y' in the month (matches only May!)
SELECT DATENAME(MONTH, insertionDate), COUNT(*) FROM [purchases]
WHERE purchaseDate LIKE '%y%'
GROUP BY DATENAME(MONTH, purchaseDate)
此 Like 技巧的用途有限，应谨慎使用，因为它依赖于技巧来获得结果。