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;
- @@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
DATEFIRST
value has an impact at DATEPART(WEEKDAY, ).
1. Set 'DATEFIRST'
Here, we are setting week start day to Sunday.
SET DATEFIRST 7;
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
DATEFIRST
with an expected week start day - Do any logical operation
- Reset
DATEFIRST
from backup after the operation
DECLARE @dbDefaultWeekStart INTEGER = @@DATEFIRST;
DECLARE @expectedWeekStart INTEGER = 6;
SET DATEFIRST @expectedWeekStart;
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 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
DateTime
object.
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
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
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;
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.
DECLARE @dateTimeNow DATETIME = GETDATE();
DECLARE @expectedWeekStart INTEGER;
SET @expectedWeekStart = 6;
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
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