目录
介绍
周默认开始日
设置周开始日
1.设置'DATEFIRST'
2.设置'DATEFIRST'并在任何逻辑操作后恢复为默认值
自定义助手函数
周函数
使用函数
参考
- 下载源代码 - 1.6 KB
本文的目的是找到DATE/DATETIME给定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, )产生影响。
在这里，我们将周开始日设置为星期日。
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)))
如果我们需要在同一个查询/会话中使用多周开始日值，我们可以这样做：
- 备份当前 @@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 Servers @@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
原文地址：https://www.codeproject.com/Tips/5161640/SQL-Server-Find-Week-Start-And-End-DateTime
