Get first day of week in SQL Server

Question

I am trying to group records by week, storing the aggregated date as the first day of the week. However, the standard technique I use for rounding off dates does not appear to work correctly with weeks (though it does for days, months, years, quarters and any other timeframe I've applied it to).

Here is the SQL:

select "start_of_week" = dateadd(week, datediff(week, 0, getdate()), 0);

This returns 2011-08-22 00:00:00.000, which is a Monday, not a Sunday. Selecting @@datefirst returns 7, which is the code for Sunday, so the server is setup correctly in as far as I know.

I can bypass this easily enough by changing the above code to:

select "start_of_week" = dateadd(week, datediff(week, 0, getdate()), -1);

But the fact that I have to make such an exception makes me a little uneasy. Also, apologies if this is a duplicate question. I found some related questions but none that addressed this aspect specifically.

Answer 1

Maybe you need this:

SELECT DATEADD(DD, 1 - DATEPART(DW, GETDATE()), GETDATE())

Or

DECLARE @MYDATE DATETIME
SET @MYDATE = '2011-08-23'
SELECT DATEADD(DD, 1 - DATEPART(DW, @MYDATE), @MYDATE)

Function

CREATE FUNCTION [dbo].[GetFirstDayOfWeek]
( @pInputDate    DATETIME )
RETURNS DATETIME
BEGIN

SET @pInputDate = CONVERT(VARCHAR(10), @pInputDate, 111)
RETURN DATEADD(DD, 1 - DATEPART(DW, @pInputDate),
               @pInputDate)

END
GO

Answer 2

I found this simple and usefull. Works even if first day of week is Sunday or Monday.

DECLARE @BaseDate AS Date

SET @BaseDate = GETDATE()

DECLARE @FisrtDOW AS Date

SELECT @FirstDOW = DATEADD(d,DATEPART(WEEKDAY,@BaseDate) *-1 + 1, @BaseDate)

Answer 3

For these that need to get:

Monday = 1 and Sunday = 7:

SELECT 1 + ((5 + DATEPART(dw, GETDATE()) + @@DATEFIRST) % 7);

Sunday = 1 and Saturday = 7:

SELECT 1 + ((6 + DATEPART(dw, GETDATE()) + @@DATEFIRST) % 7);

Above there was a similar example, but thanks to double "%7" it would be much slower.

Answer 4

CREATE FUNCTION dbo.fnFirstWorkingDayOfTheWeek
(
    @currentDate date
)
RETURNS INT
AS
BEGIN
    -- get DATEFIRST setting
    DECLARE @ds int = @@DATEFIRST 
    -- get week day number under current DATEFIRST setting
    DECLARE @dow int = DATEPART(dw,@currentDate) 

    DECLARE @wd  int =  1+(((@dow+@ds) % 7)+5) % 7  -- this is always return Mon as 1,Tue as 2 ... Sun as 7 

    RETURN DATEADD(dd,1-@wd,@currentDate) 

END

Answer 5

Since Julian date 0 is a Monday just add the number of weeks to Sunday which is the day before -1 Eg. select dateadd(wk,datediff(wk,0,getdate()),-1)

Answer 6

Set DateFirst 1;

Select 
    Datepart(wk, TimeByDay) [Week]
    ,Dateadd(d,
                CASE 
                WHEN  Datepart(dw, TimeByDay) = 1 then 0
                WHEN  Datepart(dw, TimeByDay) = 2 then -1
                WHEN  Datepart(dw, TimeByDay) = 3 then -2
                WHEN  Datepart(dw, TimeByDay) = 4 then -3
                WHEN  Datepart(dw, TimeByDay) = 5 then -4
                WHEN  Datepart(dw, TimeByDay) = 6 then -5
                WHEN  Datepart(dw, TimeByDay) = 7 then -6
                END
                , TimeByDay) as StartOfWeek

from TimeByDay_Tbl

This is my logic. Set the first of the week to be Monday then calculate what is the day of the week a give day is, then using DateAdd and Case I calculate what the date would have been on the previous Monday of that week.