[sql-server] Get first day of week in SQL Server

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.

For the basic (the current week's Sunday)

select cast(dateadd(day,-(datepart(dw,getdate())-1),getdate()) as date)

If previous week:

select cast(dateadd(day,-(datepart(dw,getdate())-1),getdate()) -7 as date)

Internally, we built a function that does it but if you need quick and dirty, this will do it.

Maybe I'm over simplifying here, and that may be the case, but this seems to work for me. Haven't ran into any problems with it yet...

CAST('1/1/' + CAST(YEAR(GETDATE()) AS VARCHAR(30)) AS DATETIME) + (DATEPART(wk, YOUR_DATE) * 7 - 7) as 'FirstDayOfWeek'
CAST('1/1/' + CAST(YEAR(GETDATE()) AS VARCHAR(30)) AS DATETIME) + (DATEPART(wk, YOUR_DATE) * 7) as 'LastDayOfWeek'

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.

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)

Googled this script:

create function dbo.F_START_OF_WEEK
(
    @DATE           datetime,
    -- Sun = 1, Mon = 2, Tue = 3, Wed = 4
    -- Thu = 5, Fri = 6, Sat = 7
    -- Default to Sunday
    @WEEK_START_DAY     int = 1 
)
/*
Find the fisrt date on or before @DATE that matches 
day of week of @WEEK_START_DAY.
*/
returns     datetime
as
begin
declare  @START_OF_WEEK_DATE    datetime
declare  @FIRST_BOW     datetime

-- Check for valid day of week
if @WEEK_START_DAY between 1 and 7
    begin
    -- Find first day on or after 1753/1/1 (-53690)
    -- matching day of week of @WEEK_START_DAY
    -- 1753/1/1 is earliest possible SQL Server date.
    select @FIRST_BOW = convert(datetime,-53690+((@WEEK_START_DAY+5)%7))
    -- Verify beginning of week not before 1753/1/1
    if @DATE >= @FIRST_BOW
        begin
        select @START_OF_WEEK_DATE = 
        dateadd(dd,(datediff(dd,@FIRST_BOW,@DATE)/7)*7,@FIRST_BOW)
        end
    end

return @START_OF_WEEK_DATE

end
go

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

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

For those who need the answer at work and creating function is forbidden by your DBA, the following solution will work:

select *,
cast(DATEADD(day, -1*(DATEPART(WEEKDAY, YouDate)-1), YourDate) as DATE) as WeekStart
From.....

This gives the start of that week. Here I assume that Sundays are the start of weeks. If you think that Monday is the start, you should use:

select *,
cast(DATEADD(day, -1*(DATEPART(WEEKDAY, YouDate)-2), YourDate) as DATE) as WeekStart
From.....

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

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)

This works wonderfully for me:

CREATE FUNCTION [dbo].[StartOfWeek]
(
  @INPUTDATE DATETIME
)
RETURNS DATETIME

AS
BEGIN
  -- THIS does not work in function.
  -- SET DATEFIRST 1 -- set monday to be the first day of week.

  DECLARE @DOW INT -- to store day of week
  SET @INPUTDATE = CONVERT(VARCHAR(10), @INPUTDATE, 111)
  SET @DOW = DATEPART(DW, @INPUTDATE)

  -- Magic convertion of monday to 1, tuesday to 2, etc.
  -- irrespect what SQL server thinks about start of the week.
  -- But here we have sunday marked as 0, but we fix this later.
  SET @DOW = (@DOW + @@DATEFIRST - 1) %7
  IF @DOW = 0 SET @DOW = 7 -- fix for sunday

  RETURN DATEADD(DD, 1 - @DOW,@INPUTDATE)

END

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.

I don't have any issues with any of the answers given here, however I do think mine is a lot simpler to implement, and understand. I have not run any performance tests on it, but it should be neglegable.

So I derived my answer from the fact that dates are stored in SQL server as integers, (I am talking about the date component only). If you don't believe me, try this SELECT CONVERT(INT, GETDATE()), and vice versa.

Now knowing this, you can do some cool math equations. You might be able to come up with a better one, but here is mine.

/*
TAKEN FROM http://msdn.microsoft.com/en-us/library/ms181598.aspx
First day of the week is
1 -- Monday
2 -- Tuesday
3 -- Wednesday
4 -- Thursday
5 -- Friday
6 -- Saturday
7 (default, U.S. English) -- Sunday
*/

--Offset is required to compensate for the fact that my @@DATEFIRST setting is 7, the default. 
DECLARE @offSet int, @testDate datetime
SELECT @offSet = 1, @testDate = GETDATE()

SELECT CONVERT(DATETIME, CONVERT(INT, @testDate) - (DATEPART(WEEKDAY, @testDate) - @offSet))

I had a similar problem. Given a date, I wanted to get the date of the Monday of that week.

I used the following logic: Find the day number in the week in the range of 0-6, then subtract that from the originay date.

I used: DATEADD(day,-(DATEPART(weekday,)+5)%7,)

Since DATEPRRT(weekday,) returns 1 = Sundaye ... 7=Saturday, DATEPART(weekday,)+5)%7 returns 0=Monday ... 6=Sunday.

Subtracting this number of days from the original date gives the previous Monday. The same technique could be used for any starting day of the week.