List Start and End of the week based on the date in one of the columns

1

how do I add columns which will return the start and end date of the week based on the date from another column?
So as an example:
in Column A I have a date: 3 June 2020,
in Column B I would like yo have a start date of that week (1 Jun 2020)
in Column C: end of that week (7 Jun 2020).

Thanks in advance

2

select '3 June 2020' as ColumnA,
convert(varchar,dateadd(wk,0,dateadd(day,1-datepart(weekday,'3 June 2020'),datediff(dd,0,'3 June 2020'))),106) as ColumnB,
convert(varchar,dateadd(wk,1,dateadd(day,0-datepart(weekday,'3 June 2020'),datediff(dd,0,'3 June 2020'))),106) as ColumnC

3

hi

another way !!! to do it

if it helps great !! :slight_smile:

please click arrow to the left for .. sample data 
create table #temp 
(
date123 date
)
go

insert into #temp select '2010-09-09'
insert into #temp select '2018-12-06'
insert into #temp select '2007-05-28'
go

select 
    DATEADD(DD,-(CHOOSE(DATEPART(dw, date123), 1,2,3,4,5,6,0)),date123) AS WeekStartDate
	,date123 as todaysDate
    ,DATEADD(DD,7-CHOOSE(DATEPART(dw, date123), 2,3,4,5,6,7,1),date123) AS WeekEndDate		 
		 from #temp
go

image

4

thanks for your help. Your suggestion helped me to arrive at the correct solution :slight_smile:
in the end I used the following (changed my query to report last day of prior week and last day of current week:
DATEADD(wk,DATEDIFF(wk,7,@DATE-1),6) as LastSunday, DATEADD(wk,DATEDIFF(wk,0,@DATE-1),6) as CurrentSunday

5

thanks for your help. Your suggestion helped me to arrive at the correct solution :slight_smile: big thanks!