Get the current days of the week from Monday to Sunday

Question

I tried the solution for this seems to be.

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

he gives Saturday-Saturday as a result, but on Sunday he gives me days next week

I want Sunday as the last day of the week and Monday as the first day of the week.

Please, help...

Answer 1

In general, use SET DATEFIRST 1 to indicate that Monday is the first day of the week. However, this does not solve the problem 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

Install DATEFIRST (Transact-SQL)

 1 Monday 2 Tuesday 3 Wednesday 4 Thursday 5 Friday 6 Saturday 7 (default, US 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

It can be overly complicated, but it was fun.

- This first part is to get the last time on Monday.

- starts by creating a table that will contain all dates until the last Monday, and then sets the min of this 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 until 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)'