Share via

Last 3 full months vs last year same 3 months dates

freemont312 21 Reputation points
2020-10-14T13:02:14+00:00

Hi Guys

I am looking for a way to use my date in SQL Server to show me the "last 3 full months vs last year same 3 months dates" not adding the current month - this is something that I want to hard code (Store Proc) and use in SSRS in a Date parameter. Or if I can specify X number of Months would be awesome like (last 2 months, last 3 months, last 12 Months).

so for instance we are in the Month of October 2020 - when I run the SQL I want the dates to show:

July 2020
August 2020
September 2020
July 2019
August 2019
September 2019

Thanks for all the feedback
I really learn a lot from you guys.

Developer technologies | Transact-SQL
SQL Server | Other
0 comments
Accepted answer
  1. Thomas LeBlanc 481 Reputation points
    2020-10-14T13:29:43.963+00:00

    SELECT GetDate()

    SELECT dateadd(m, -3, GetDate()), dateadd(m, -2, GetDate()), dateadd(m, -1, GetDate())

    SELECT DATENAME(month, dateadd(m, -3, GetDate())) + ' ' + DATENAME(year, dateadd(m, -3, GetDate()))

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Thomas LeBlanc 481 Reputation points
    2020-10-14T14:26:05.16+00:00

    declare @SD date = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-3, 0)
    declare @ED date = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
    declare @YSD Tech date = DATEADD( YEAR, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-3, 0))
    declare @YED date = DATEADD( YEAR, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))

    Select createat
    from orders
    where
    ( createdat >= @SD AND createdat < @ED)
    OR
    ( createdat >= @YSD Tech AND createdat < @YED)

    1 person found this answer helpful.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.