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...

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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)

Answer 5

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)'

Answer 6

CREATE PROCEDURE forweek

( @year varchar(max) --,@SearchParam varchar(max)

)

AS BEGIN Declare @StartDate date, @d date, @f date, @l date, @currentyear varchar(max);

 Set @year=@year
 Set @currentyear =(SELECT YEAR(getdate()) as year)

if ( @year = @currentyear) begin SET @d = dateadd(week,datediff(week,0,getdate())-datediff(wk,@year+'-01-01',getdate()+1),0) --start of week from a year ago SET @f = dateadd(week,datediff(week,0,getdate()-1),0) --start of current partial week; SET @l = dateadd(week,datediff(week,0,getdate())-datediff(wk,@year+'-01-01',getdate()+1),6) create table #weeks (dateid int IDENTITY(1,1) PRIMARY KEY, week_starting date , week_End date )

while @d <= @f begin insert into #weeks (week_starting,week_End) values (@d,@l) set @d = dateadd(week,1,@d) set @l = dateadd(week,1,@l) end --select * from #weeks Select (convert(varchar, w.week_starting, 101)+'-'+ convert(varchar, w.week_End, 101)) as weekrange, w.week_starting as weekstarting ,w.week_End as weekEnd from #weeks w order by w.dateid desc --where (convert(varchar, w.week_starting, 101)+'-'+ convert(varchar, w.week_End, 101)) like '%' + @SearchParam + '%'

drop table #weeks end

else begin SET @d = dateadd(week,datediff(week,0,@year+'-12-31')-datediff(wk,@year+'-01-01',@year+'-12-31')+1,0) --start of week from a year ago SET @f = dateadd(week,datediff(week,0,@year+'-12-31'),6) --start of current partial week; SET @l = dateadd(week,datediff(week,0,@year+'-12-31')-datediff(wk,@year+'-01-01',@year+'-12-31')+1,6) create table #weeks1 (dateid int IDENTITY(1,1) PRIMARY KEY, week_starting date , week_End date )

while @d <= @f begin insert into #weeks1 (week_starting,week_End) values (@d,@l) set @d = dateadd(week,1,@d) set @l = dateadd(week,1,@l) end --select * from #weeks1 Select (convert(varchar, w.week_starting, 101)+'-'+ convert(varchar, w.week_End, 101)) as weekrange, w.week_starting as weekstarting ,w.week_End as weekEnd from #weeks1 w --where (convert(varchar, w.week_starting, 101)+'-'+ convert(varchar, w.week_End, 101)) like '%' + @SearchParam + '%' order by w.dateid desc drop table #weeks1

end