CODEWITHSUNDEEP
sqlsql-serversql-server-2008
donviti
donviti

Reputation: 294

Prior Month for Prior Year

I have been able to Declare and Set grabbing the prior months data. 

DECLARE @FirstDayofPrevMonth datetime
SET @FirstDayofPrevMonth = DATEADD(mm, DATEDIFF(m,0,GETDATE())-1,0)
DECLARE @LastDayofPrevMonth datetime  
SET @LastDayofPrevMonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

What I now need is to be able to set and declare prior month, prior year....

to be clear, this does work for me. I get the expected result which is Aug 2014. What I'm looking to do, is get Aug 2013 and so on.

UPDATE: I thought I would share two other pieces I added to my code that may be helpful to others.

I used the below answer and added to it

DECLARE @FirstDayofPrevMonth datetime
SET @FirstDayofPrevMonth = DATEADD(mm, DATEDIFF(m,0,GETDATE())-1,0)
DECLARE @LastDayofPrevMonth datetime
SET @LastDayofPrevMonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

-- to be used for the prior year completed month

DECLARE @FirstDayofPrevMonthLY datetime
SET @FirstDayofPrevMonthLY = DATEADD(year,-1,@FirstDayofPrevMonth)
DECLARE @LastDayofPrevMonthLY datetime
SET @LastDayofPrevMonthLY = DATEADD(year,-1,@LastDayofPrevMonth)

--This is to be used if you want for dates in the current Quarter

DECLARE @FirstDayofCQ datetime
SET @FirstDayofCQ = DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)
DECLARE @LastDayofCQ datetime
SET @LastDayofCQ =DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) + 1, 0)

--This is to be used for the Prior Quarter

DECLARE @FirstDayofPrevQ datetime
SET @FirstDayofPrevQ = DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) - 1, 0)
DECLARE @LastDayofPrevQ datetime
SET @LastDayofPrevQ = DATEADD(dd, -1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0))

Upvotes: 0

Views: 122

Answers (2)

Donal
Donal

Reputation: 32713

DECLARE @FirstDayofPrevMonthPrevYear datetime

SET @FirstDayofPrevMonthPrevYear = (DATEADD(yy, -1, DATEADD(mm, DATEDIFF(m,0,GETDATE())-1,0)))

Upvotes: 1

radar
radar

Reputation: 13425

DECLARE @FirstDayofPrevMonthPriorYear datetime  
DECLARE @LastDayofPrevMonthPriorYear datetime  
SET @FirstDayofPrevMonthPriorYear = DATEADD(year,-1,@FirstDayofPrevMonth)
SET @LastDayofPrevMonthPriorYear = DATEADD(year,-1,@LastDayofPrevMonth)

Upvotes: 2

Related Questions