SQL Server: DateTime Range Pro
3.59/5 (8投票s)
在 SQL Server 中填充 DateTime 范围
引言
在报表中，通常需要根据日期时间属性列出/返回数据。这可能包括创建各种报告，例如每日、每周、每月、每年等。本文将特别帮助那些需要生成各种日期范围的人。文章和示例侧重于 DATETIME 数据类型。
背景
我们将要做什么？
- 查找一天的开始和结束日期时间
- 填充具有范围的天数
- 查找一周的开始和结束日期时间
- 填充具有范围的周数
- 查找一个月的开始和结束日期时间
- 填充具有范围的月份
- 查找一年的开始和结束日期时间
- 填充具有范围的年份
- 其他 DateTime 相关事项
日
日范围
查找日期的开始和结束日期时间。
DECLARE @dateTimeNow DATETIME ='2019-07-01 17:20:00' /*yyyy-MM-dd HH:mm:ss*/
--DECLARE @dateTimeNow DATETIME = GETDATE(); /*now*/
SELECT
[StartDateTime] = DATEADD(DAY, DATEDIFF(DAY, 0, @dateTimeNow), 0),
[EndDateTime] = DATEADD(SECOND, -1, DATEADD(DAY, DATEDIFF(DAY, 0, @dateTimeNow) + 1, 0));
用范围填充天数
生成具有每日范围的日期列表
DECLARE @startDateTime DATETIME ='2019-09-21', _
@endDateTime DATETIME ='2019-09-30'; /*yyyy-MM-dd*/
--SET @startDateTime = GETDATE(); _
SET @endDateTime = @startDateTime + 10; /*now*/
WITH Dates([Date])
AS
(
SELECT [Date]= @startDateTime
UNION ALL
SELECT [Date] + 1
FROM Dates
WHERE [Date] + 1 <= @endDateTime
),
DateRange([Date], [StartDateTime], [EndDateTime])
AS
(
SELECT
[Date],
DATEADD(DAY, DATEDIFF(DAY, 0, [Date]), 0),
DATEADD(SECOND, -1, DATEADD(DAY, DATEDIFF(DAY, 0, [Date]) + 1, 0))
FROM Dates
)
SELECT *
FROM DateRange
OPTION (MAXRECURSION 0)
周
周范围
查找一周的开始和结束日期时间。
DECLARE @dateTimeNow DATETIME ='2019-07-01' /*yyyy-MM-dd*/
--DECLARE @dateTimeNow DATETIME = GETDATE(); /*now*/
SELECT
[StartDate] = DATEADD(dd, -(DATEPART(WEEKDAY, @dateTimeNow)-1), _
DATEADD(dd, DATEDIFF(dd, 0, @dateTimeNow), 0)),
[EndDate] = DATEADD(dd, 7-(DATEPART(WEEKDAY, @dateTimeNow)), _
DATEADD(dd, DATEDIFF(dd, 0, @dateTimeNow), 0));
SELECT
[StartDateTime] = DATEADD(DAY, -(DATEPART(WEEKDAY, @dateTimeNow)-1), _
DATEADD(DAY, DATEDIFF(DAY, 0, @dateTimeNow), 0)),
[EndDateTime] = DATEADD(DAY, 7-(DATEPART(WEEKDAY, @dateTimeNow)), _
DATEADD(SECOND, -1, DATEADD(DAY, DATEDIFF(DAY, 0, @dateTimeNow) + 1, 0)))
用范围填充周数
生成具有每周范围的周列表
DECLARE @startDateTime DATETIME = '2019-04-01 03:20:00', _
@endDateTime DATETIME = '2019-04-30 03:20:00';
WITH Weeks([Date])
AS
(
SELECT [Date] = @startDateTime
UNION ALL
SELECT DATEADD(DAY, 7, [Date])
FROM Weeks
WHERE DATEADD(DAY, 7, [Date]) <= @endDateTime
),
WeekRange([Date], [StartDateTime], [EndDateTime])
AS
(
SELECT
[Date],
DATEADD(DAY, -(DATEPART(WEEKDAY, [Date])-1), _
DATEADD(DAY, DATEDIFF(DAY, 0, [Date]), 0)),
DATEADD(DAY, 7-(DATEPART(WEEKDAY, [Date])), _
DATEADD(SECOND, -1, DATEADD(DAY, DATEDIFF(DAY, 0, [Date]) + 1, 0)))
FROM Weeks
)
SELECT *
FROM WeekRange
OPTION (MAXRECURSION 0)
更改周开始日
在 SQL Server 中，星期日是默认的周开始日。有一个 @@DATEFIRST 函数，它返回当前的周开始日（SET DATEFIRST 的值）。要更改默认的周开始日，我们可以将任何 周开始日值（介于 1-7 之间） 设置为 DATEFIRST。设置预期的周开始日后，我们只需运行上述查询即可。
SELECT @@DATEFIRST;
SET DATEFIRST 7; /*setting week start to 'Sunday'*/
@@DATEFIRST 是会话本地的。我们可以在 SQL Server Management Studio 中打开不同的选项卡，并在不同的选项卡中执行 set/select 代码来验证它。有关选项，请查看 SQL Server: Find Week Start And End DateTime。
月
月范围
查找一个月的开始和结束日期时间。
DECLARE @dateTimeNow DATETIME ='2019-07-01'; /*yyyy-MM-dd*/
--DECLARE @dateTimeNow DATETIME = GETDATE(); /*now*/
SELECT
[StartDate] = DATEADD(mm, DATEDIFF(m, 0, @dateTimeNow), 0),
[EndDate] = DATEADD(mm, DATEDIFF(m, 0, @dateTimeNow) + 1, -1);
SELECT
[StartDateTime] = DATEADD(mm, DATEDIFF(m, 0, @dateTimeNow), 0),
[EndDateTime] = DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, @dateTimeNow) + 1, 0));
用范围填充月份
生成具有每月范围的月列表
DECLARE @startDateTime DATETIME ='2019-01-18 03:20:00', _
@endDateTime DATETIME ='2019-12-18 04:20:00';
WITH Months([Date])
AS
(
SELECT [Date] = @startDateTime
UNION ALL
SELECT DATEADD(MONTH, 1, [Date])
FROM Months
WHERE DATEADD(MONTH, 1, [Date]) <= @endDateTime
),
MontRange([Date], [StartDateTime], [EndDateTime])
AS
(
SELECT
[Date],
DATEADD(mm, DATEDIFF(m, 0, [Date]), 0),
DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, [Date]) + 1, 0))
FROM Months
)
SELECT *
FROM MontRange
OPTION (MAXRECURSION 0)
年份
年范围
查找一年的开始和结束日期时间。
DECLARE @dateTimeNow DATETIME ='2019-07-01' /*yyyy-MM-dd*/
--DECLARE @dateTimeNow DATETIME = GETDATE(); /*now*/
SELECT
[StartDate] = DATEADD(yy, DATEDIFF(yy, 0, @dateTimeNow), 0),
[EndDate] = DATEADD(yy, DATEDIFF(yy, 0, @dateTimeNow) + 1, -1)
SELECT
[StartDateTime] = DATEADD(yy, DATEDIFF(yy, 0, @dateTimeNow), 0),
[EndDateTime] = DATEADD(s, -1, DATEADD(yy, DATEDIFF(yy, 0, @dateTimeNow) + 1, 0))
用范围填充年份
生成具有每年范围的年列表
DECLARE @startDateTime DATETIME ='2017-12-17 03:20:00', _
@endDateTime DATETIME ='2019-12-19 04:20:00';
WITH Years([Date])
AS
(
SELECT [Date] = @startDateTime
UNION ALL
SELECT DATEADD(YEAR, 1, [Date])
FROM Years
WHERE DATEADD(YEAR, 1, [Date]) <= @endDateTime
),
YearRange([Date], [StartDateTime], [EndDateTime])
AS
(
SELECT
[Date],
DATEADD(yy, DATEDIFF(yy, 0, [Date]), 0),
DATEADD(s, -1, DATEADD(yy, DATEDIFF(yy, 0, [Date]) + 1, 0))
FROM Years
)
SELECT *
FROM YearRange
OPTION (MAXRECURSION 0)
DateTime 函数
在重复相同的代码多次后，我一直在想为什么不创建一个日期时间辅助函数来查找开始/结束日期时间。所以我们现在有了它。
创建函数
IF OBJECT_ID(N'DateTimePart', N'FN') IS NOT NULL
DROP FUNCTION DateTimePart;
GO
CREATE FUNCTION DateTimePart(@dateTime DATETIME, @startOrEnd VARCHAR(10))
RETURNS DATETIME
AS
BEGIN
/*validations*/
IF @dateTime IS NULL
BEGIN
RETURN @dateTime;
END
IF @startOrEnd NOT IN('Start', 'End')
BEGIN
RETURN CAST('@startOrEnd should be IN(Start, End)' AS INT);
END
/*result*/
DECLARE @result DATETIME;
SELECT @result =
CASE @startOrEnd
WHEN 'Start' THEN DATEADD(DAY, DATEDIFF(DAY, 0, @dateTime), 0)
WHEN 'End' THEN DATEADD(SECOND, -1, _
DATEADD(DAY, DATEDIFF(DAY, 0, @dateTime) + 1, 0))
END;
RETURN @result;
END
使用函数
DECLARE @dateTime DATETIME ='2019-12-01 17:20:00' /*yyyy-MM-dd HH:mm:ss*/
SELECT
[DateTime] = @dateTime,
[StartDateTime] = dbo.DateTimePart(@dateTime, 'start'),
[EndDateTime] = dbo.DateTimePart(@dateTime, 'end');
其他
DateTime 现在
SELECT
[Local] = GETDATE(),
[Utc] = GETUTCDATE();
DateTime 到日期
SELECT
[Date] = CONVERT(DATE, GETDATE()), --CONVERT(DATE, GETDATE(), 101)
[Date] = CAST(GETDATE() AS DATE),
[DateTime] = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0); --DAY
日、月、年详细信息
名称
SELECT
[Day] = DATENAME(WEEKDAY, GETDATE()),
[Month] = DATENAME(MONTH, GETDATE()),
[Year] = DATENAME(YEAR, GETDATE());
数字
SELECT
[Day] = DATEPART(WEEKDAY, GETDATE()),
[Month] = DATEPART(MONTH, GETDATE()),
[Year] = DATEPART(YEAR, GETDATE());
添加到日期
添加一天
SELECT
[Today] = GETDATE(),
[TodayPlusOneDayUsingFunction] = DATEADD(dd, 1, GETDATE()), /*addsing one day*/
[TodayPlusOneDayUsingOperator] = GETDATE() + 1; /*addsing one day*/
减少一秒
SELECT
[NowDateTime] = GETDATE(),
[StatDateTime] = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0), /*removing time details*/
[EndDateTime] = DATEADD(SECOND, -1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0)); /*removing time details, adding one date(+1), deducting one second(-1)*/
减少毫秒
要添加/减少 MILLISECOND/MICROSECOND/NANOSECOND，最好将源/结果转换为 DATETIME2 对象，而不是 DATETIME。
SELECT
[NowDateTime] = GETDATE(),
DATEADD(MILLISECOND, -1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0)), /*not as expected, went to next date*/
/*
MICROSECOND, MICROSECOND, NANOSECOND need DATETIME2, better to use DATETIME2 data type
Datetime2 Vs Datetime: https://stackoverflow.com/questions/1334143/datetime2-vs-datetime-in-sql-server
*/
DATEADD(MILLISECOND, -1, CAST(DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0) AS DATETIME2)),
DATEADD(MICROSECOND, -1, CAST(DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0) AS DATETIME2)),
DATEADD(NANOSECOND, -1, CAST(DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0) AS DATETIME2));
按日期分组
DECLARE @tblTest TABLE(AddDateTime DATETIME NOT NULL);
INSERT
INTO @tblTest
VALUES
/*yyyy-MM-dd hh:mm:ss*/
('2019-04-17 03:20:00'),
('2019-04-17 04:20:00'),
('2019-04-16 03:20:00'),
('2019-04-16 04:20:00')
SELECT
[Date] = DATEADD(dd, DATEDIFF(dd, 0, AddDateTime), 0),
[Total] = COUNT(AddDateTime)
FROM @tblTest
GROUP BY DATEADD(dd, DATEDIFF(dd, 0, AddDateTime), 0);
限制
该代码对于未经测试的输入可能会抛出意外错误。如果有，请告诉我。
历史
- 2019年7月11日：初始版本