SQL Server：查找周开始和结束日期时间
3.68/5 (7投票s)
从给定日期时间中查找周开始和结束日期时间
引言
本文的目的是为给定的DATETIME对象查找周开始和结束的DATE/DATETIME。
我们要干什么
- 检查 SQL Server 默认功能
- 探索其他选项和技术
- 使用自定义函数
以默认日为周开始
默认情况下，SQL Server 的周开始日期是星期日。在这里，我们正在为当前DateTime填充周DATE/DATETIME范围。
DECLARE @dateTimeNow DATETIME = GETDATE();
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)))
设置周开始日
在 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 代码来验证这一点。
DATEFIRST值的变化会影响DATEPART(WEEKDAY, )。
1. 设置 'DATEFIRST'
在这里，我们将周开始日设置为星期日。
SET DATEFIRST 7; /*setting week start to 'Sunday'*/
DECLARE @dateTimeNow DATETIME = GETDATE();
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)))
2. 设置 'DATEFIRST' 并在任何逻辑操作后恢复为默认值
如果我们需要在同一个查询/会话中使用多个周开始日值，我们可以这样做
- 备份当前的
@@DATEFIRST - 使用期望的周开始日设置
DATEFIRST - 执行任何逻辑操作
- 在操作后从备份中重置
DATEFIRST
DECLARE @dbDefaultWeekStart INTEGER = @@DATEFIRST; /*take backup of db default week start day*/
DECLARE @expectedWeekStart INTEGER = 6; /*expected week start from 'Saturday'*/
SET DATEFIRST @expectedWeekStart; /*set week start day as expected*/
/*doing our calculation as needed*/
DECLARE @dateTimeNow DATETIME = GETDATE();
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)))
/*reset week start date to its default or as it was*/
SET DATEFIRST @dbDefaultWeekStart;
自定义辅助函数
周函数
在这里，我们将创建一个周辅助函数...
它将接受输入
@weekStartDay INTEGER- 是必需的，应该在 1-7之间@dateTime DATETIME- 是必需的@weekPart VARCHAR(10)-是必需的，应该在（'Start', 'Middle', 'End'）之间
并将输出
- 期望的周部分（'Start', 'Middle', 'End'）作为
DateTime对象。
/*create function*/
IF OBJECT_ID(N'WeekPart', N'FN') IS NOT NULL
DROP FUNCTION WeekPart;
GO
CREATE FUNCTION WeekPart(@weekStartDay INTEGER, @dateTime DATETIME, @weekPart VARCHAR(10))
RETURNS DATETIME
AS
BEGIN
/*validations*/
IF @dateTime IS NULL
BEGIN
RETURN @dateTime;
END
IF @weekStartDay NOT BETWEEN 1 AND 7
BEGIN
RETURN CAST('week start day value should be BETWEEN 1 AND 7' AS INT);
END
IF @weekPart NOT IN('Start', 'Middle', 'End')
BEGIN
RETURN CAST('week part should be IN(Start, Middle, End)' AS INT);
END
/*date to day number: https://docs.microsoft.com/en-us/sql/t-sql/statements/
set-datefirst-transact-sql?view=sql-server-2017*/
DECLARE @dayNumber INTEGER;
SELECT @dayNumber =
CASE DATENAME(WEEKDAY, @dateTime)
WHEN 'Monday' THEN 1
WHEN 'Tuesday' THEN 2
WHEN 'Wednesday'THEN 3
WHEN 'Thursday' THEN 4
WHEN 'Friday' THEN 5
WHEN 'Saturday' THEN 6
WHEN 'Sunday' THEN 7
END;
/*calculate result*/
DECLARE @difference INTEGER = -((7+@dayNumber-@weekStartDay)%7);
DECLARE @startDateTime DATETIME = DATEADD(dd, @difference, @dateTime);
DECLARE @resultDateTime DATETIME;
SELECT @resultDateTime =
CASE @weekPart
WHEN 'Start' THEN @startDateTime
WHEN 'Middle' THEN DATEADD(dd, 3, @startDateTime)
WHEN 'End' THEN DATEADD(dd, 6, @startDateTime)
ELSE @dateTime
END;
RETURN @resultDateTime;
END;
使用该函数
让我们在查询中使用创建的函数，我们将星期六设置为周开始日。如果需要，我们甚至可以将 SQL Server 的@@DATEFIRST值作为参数传递。
/*result*/
DECLARE @dateTimeNow DATETIME = GETDATE();
DECLARE @expectedWeekStart INTEGER;
SET @expectedWeekStart = 6; /*set 'Saturday', without changing db default*/
--SELECT @expectedWeekStart = @@DATEFIRST; /*using db default*/
SELECT
[NowDate] = CAST(@dateTimeNow AS DATE),
[WeekStartDate] = CAST(dbo.WeekPart(@expectedWeekStart, @dateTimeNow, 'start') AS DATE),
[WeekMiddleDate] = CAST(dbo.WeekPart(@expectedWeekStart, @dateTimeNow, 'middle') AS DATE),
[WeekEndDate] = CAST(dbo.WeekPart(@expectedWeekStart, @dateTimeNow, 'end') AS DATE);
SELECT
[NowDate] = DATEADD(dd, DATEDIFF(dd, 0, @dateTimeNow), 0),
[WeekStartDateTime] = DATEADD(dd, DATEDIFF(dd, 0, _
dbo.WeekPart(@expectedWeekStart, @dateTimeNow, 'start')), 0),
[WeekMiddleDateTime] = DATEADD(dd, DATEDIFF(dd, 0, _
dbo.WeekPart(@expectedWeekStart, @dateTimeNow, 'middle')), 0),
[WeekEndDate] = DATEADD(SECOND, -1, DATEADD(DAY, DATEDIFF(DAY, 0, _
dbo.WeekPart(@expectedWeekStart, @dateTimeNow, 'end')) + 1, 0));
参考文献
- 周开始和结束：https://stackoverflow.com/a/1267176/2948523
- @@DATEFIRST 的日期编号：https://docs.microsoft.com/en-us/sql/t-sql/statements/set-datefirst-transact-sql?view=sql-server-2017
- @@DATEFIRST 对会话是局部的：https://stackoverflow.com/questions/883127/sql-server-set-datefirst-scope
- 查找星期名称：https://database.guide/3-ways-to-get-the-day-name-from-a-date-in-sql-server-t-sql/
- 规范化一周的第一天：https://www.itprotoday.com/sql-server/normalizing-first-day-week
源代码
请将 SQL 代码文件作为附件找到。
限制
这是一个学习目的的帖子。未经测试的输入代码可能会抛出意外错误。如果有，请告诉我。
历史
- 2019年7月10日：初始版本