SQL Server: Find Week Start And End DateTime
3.68/5 (7 votes)
Finding week start and end date time from a given date time
Introduction
The aim of this article is to find the week start and end DATE/DATETIME for a given DATETIME object.
What are we going to do:
- Check the SQL Server default feature
- Explore other options and techniques
- Use a custom function
Week Start With Default Day
By default, SQL Server week start date is Sunday. Here, we are populating week DATE/DATETIME range for current 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)))
Set Week Start Day
In SQL Server, there is a @@DATEFIRST function, which returns the current week start day (value of SET DATEFIRST). To change default week start day, we can set any week start day value between 1-7 to DATEFIRST.
SELECT @@DATEFIRST;
SET DATEFIRST 7; /*setting week start to 'Sunday'*/
- @@DATEFIRST is local to the session. We can verify it by opening different tabs in SQL Server Management Studio and executing set/select code in the different tabs.
- Change in
DATEFIRSTvalue has an impact atDATEPART(WEEKDAY, ).
1. Set 'DATEFIRST'
Here, we are setting week start day to Sunday.
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. Set 'DATEFIRST' And Restore To Default After Any Logical Operation
If we need to use multiple weeks start day value in the same query/session, we can do:
- Take a backup of the current
@@DATEFIRST - Set
DATEFIRSTwith an expected week start day - Do any logical operation
- Reset
DATEFIRSTfrom backup after the operation
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;
Custom Helper Function
Week Function
Here, we are going to create a week helper function...
Which will take inputs:
@weekStartDay INTEGER- is required and should be any between 1-7@dateTime DATETIME- is required@weekPart VARCHAR(10)-is required and should be any among ('Start', 'Middle', 'End')
and will output:
- Expected week part ('Start', 'Middle', 'End') as
DateTimeobject.
/*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;
Using the Function
Let's use the created function in the query, where we are setting Saturday as the week start day. If needed, we can even pass SQL Servers @@DATEFIRST value as a parameter.
/*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));
References
- Week start and end: https://stackoverflow.com/a/1267176/2948523
- @@DATEFIRST day numbers: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-datefirst-transact-sql?view=sql-server-2017
- @@DATEFIRST is local to session: https://stackoverflow.com/questions/883127/sql-server-set-datefirst-scope
- Find day name: https://database.guide/3-ways-to-get-the-day-name-from-a-date-in-sql-server-t-sql/
- Normalizing the first day of a week: https://www.itprotoday.com/sql-server/normalizing-first-day-week
Source Code
Please find the SQL code file as an attachment.
Limitations
This is a learning purpose post. The code may throw unexpected errors for untested inputs. If any, just let me know.
History
- 10th July, 2019: Initial version