How to get period of 4 weeks previous

Question

With this code, I got period of last 4 weeks successfully.

Declare @MyDate Date
Set @MyDate = '31 Mar 2023'

select dateadd(week,-4,@MyDate) as StartDate,@MyDate as EndDate

However, I want startdate display as 1st March if it is the same month as enddate. e.g.When @MyDate = '31 Mar 2023' I want 1st March as startdate, when @MyDate = '14 Mar 2023' I want 14 Feb as startdate.

Please help.

Answer 1

Hi @Siver

It seems that you need to use CASE WHEN.

Please check this:

SELECT CASE WHEN MONTH(DATEADD(WEEK,-4,@MyDate)) = MONTH(@mydate) 
            THEN DATEADD(dd, -( DAY( @mydate ) -1 ), @mydate)
			ELSE DATEADD(WEEK,-4,@MyDate) 
			END AS StartDate
	  ,@MyDate AS EndDate 

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.