Get Current week days from monday to sunday

Question

I have tried solution for this is like.

select dateadd(wk, datediff(wk, 0, getdate()), 0)as StartDate ,
   (select dateadd(wk, datediff(wk, 0, getdate()), 0) + 5) as EndDate

it gives monday-saturday in result, but on Sunday it gives me next week days

I want sunday as last day of week and Monday as First Day of week..

Please Help...

You need to take a look to this answer [Get first day of week in SQL Server](http://stackoverflow.com/a/7169656/1297603) — Yaroslav, Oct 08 '13 at 07:23

Tim Schmelter · Accepted Answer · 2013-10-08T07:23:03.600

12

In general, use SET DATEFIRST 1 to specify that monday is the first day of the week. However, that doesn't solve the issue here. Use this syntax instead:

SELECT DATEADD(week, DATEDIFF(day, 0, getdate())/7, 0) AS StartWeek,
       DATEADD(week, DATEDIFF(day, 0, getdate())/7, 5) AS EndWeek

Demo

SET DATEFIRST (Transact-SQL)

1    Monday
2    Tuesday
3    Wednesday
4    Thursday
5    Friday
6    Saturday
7    (default, U.S. English) Sunday

edited Oct 08 '13 at 07:23

answered Oct 08 '13 at 07:13

Tim Schmelter

429,027
67
649
891

thanks a lot, i got the required solution from this. – saylesh Oct 08 '13 at 08:51
Perfect answer. Thanks – Renish Patel May 02 '17 at 11:13
it show only days from Monday to today not all days of week – Apr 01 '21 at 21:16
@csharp_devloper31 yes, but that was OP's requirement if you look at his question. Maybe he used the wrong title – Tim Schmelter Apr 02 '21 at 09:27
No , please the question again , he said " I want sunday as last day of week and Monday as First Day of week.." – Apr 02 '21 at 09:50
1

@csharp_devloper31 well you can read his comment, his approach and he accepted it. So i guess i understood it correctly. But if you want all days of the current week im sure there is another question on stackoverflow which answers it. If not feel free to ask it. Best way is to create a calendar table if you dont have it already. – Tim Schmelter Apr 02 '21 at 09:58

score 1 · Answer 2 · edited Nov 18 '13 at 04:53

1

You just add 6 days instead of 5.

select dateadd(wk, datediff(wk, 0, getdate()), 0) as StartDate 
select dateadd(wk, datediff(wk, 0, getdate()), 0) + 6) as EndDate

edited Nov 18 '13 at 04:53

Michael Petrotta

58,479
27
141
176

answered Nov 18 '13 at 04:34

Vicky

11
1

score 0 · Answer 3 · edited Jul 22 '14 at 15:04

DECLARE
    @d datetime,
    @f datetime;

SET @d = dateadd(week,datediff(week,0,getdate())-48,0) --start of week from a year ago
SET @f = dateadd(week,datediff(week,0,getdate()),0) --start of current partial week;

create table #weeks (
    week_starting datetime primary key
)

while @d < @f
begin
    insert into #weeks (week_starting) values (@d)
    set @d = dateadd(week,1,@d)
end
select * from #weeks

drop table #weeks

score 0 · Answer 4 · answered Aug 06 '14 at 18:42

This may be excessively complicated, but it was tons of fun.

--This first part is to get the most recently occurred monday.

--It starts by creating a table which will hold all dates until the most recent monday, then sets the min of that table to the @mondaythisweek variable.

declare @dateholder table (
    thedate date,
    theday varchar(10)
    )

declare @now datetime
set @now = GETDATE()

;with mycte as (
    select
        cast(@now as date) as "thedate",
        DATENAME(dw,@now) as "theday"
    union all
    select 
        cast(DATEADD(d,-1,"thedate") as date) as "thedate",
        DATENAME(DW,DATEADD(d,-1,"thedate")) as "theday"
    from
        mycte
    where
        "theday" <> 'Monday'
    )
insert into @dateholder
select * from mycte 
option (maxrecursion 10)

declare @mondaythisweek date
set @mondaythisweek  = (
select min(thedate)
from @dateholder
)

--This part creates a table from @mondaythisweek to the next sunday

;with mon_to_sun as (
    select
        @mondaythisweek as "dates",
        DATENAME(dw,@mondaythisweek) as "theday"
    union all 
    select
        cast(DATEADD(d,1,"dates") as date) as "dates",
        DATENAME(dw,cast(DATEADD(d,1,"dates") as date)) as "theday"
    from mon_to_sun
    where "theday" <> 'Sunday'
)
select * 
from mon_to_sun 
option(maxrecursion 10)

imsanjaysc · Answer 5 · 2019-10-06T12:03:50.470

0

SELECT DATEADD(week, DATEDIFF(day, 0, GETDATE())/7, 0) AS 'StartWeek(Monday)',
       DATEADD(week, DATEDIFF(day, 0, GETDATE())/7, 6) AS 'EndWeek(Sunday)'

With Time

SELECT DATEADD(week, DATEDIFF(day, 0, GETDATE())/7, 0) AS 'StartWeek(Monday)',
       DATEADD(DAY,DATEDIFF(day, 0, DATEADD(week, DATEDIFF(day, 0, GETDATE())/7, 6)), '23:59:59') AS 'EndWeek(Sunday)'

edited Oct 06 '19 at 12:03

answered Oct 06 '19 at 11:54

imsanjaysc

101
7

Get Current week days from monday to sunday

5 Answers5