How can I get the last 12 weeks from the current date PLUS extra days till the start of the week if the week start at Sunday

Question

Getting the last 12 weeks from a specific date is easy and can be retrieved by the following command in SQL-server. Its answer is 2014-08-17.

select Dateadd(Week, -12, '2015-08-17')

What I want is to get the last 12 weeks but ending at the end of week. I dont want to see last week of 6, 5, 4, 3, 2, 1 days. Please help.

Please give an example of your desired results. The question is not clear. — Tab Alleman, Aug 17 '15 at 13:56
It is similar to my this question http://stackoverflow.com/questions/32049478/how-can-i-get-the-last-12-months-from-the-current-date-plus-extra-days-till-1st] — Dr. Mian, Aug 17 '15 at 13:57

score 1 · Answer 1 · edited May 23 '17 at 12:14

1

The easy way is to break it down to pieces:

First, Find the date of the last Sunday (thanks to Andriy M)

Then all you have to do is use DateAdd(week, -12, @LastSunday).

Here is the full script:

DECLARE @Date datetime = GETDATE(),
        @LastSunday datetime,
        @TargetDate datetime


SET @LastSunday = DATEADD(day,
                          -1 - (DATEPART(dw, @Date) + @@DATEFIRST - 2) % 7,
                          @Date
                          )

SET @TargetDate = DATEADD(week, -12, @LastSunday)

Play with it your self in this sql fiddle..

edited May 23 '17 at 12:14

Community

1
1

answered Aug 17 '15 at 14:15

Zohar Peled

79,642
10
69
121

One problem is that I also want to show data of the current week. Any idea? – Dr. Mian Aug 18 '15 at 09:36
why is that a problem? just get the data between `@TargetDate` (12 weeks ago) and `@NextSaturday` (If you already know how to get last Sunday, surly you can figure out how to get next Saturday...) – Zohar Peled Aug 18 '15 at 13:58

How can I get the last 12 weeks from the current date PLUS extra days till the start of the week if the week start at Sunday

1 Answers1