CODEWITHSUNDEEP
sqlsql-server
Vadiya
Vadiya

Reputation: 111

How to get previous -previous quarters month and year in SQL Server

Did this for Month:

declare @month int;
declare @quarter int

set @quarter= DATEPART(Quarter, '2020-10-14')
set @month = (@quarter - 2) *3

select @month
select @quarter

Please do help me with the year. I have searched online gone through Datediff and dateadd functions but it is confusing

Upvotes: 0

Views: 6591

Answers (3)

Caius Jard
Caius Jard

Reputation: 74730

I think I'd find it easier to adjust the date to the first day of the final month of the current quarter then take 6 months off it:

declare @quart int;
declare @month int;
declare @thedate date;
set @thedate = '2020-07-14';
set @quart = DATEPART(quarter, @thedate);
set @month = ((@quart - 1) * 3) + 2
set @thedate = DATEFROMPARTS(@year, @month, 1)
set @thedate = DATEADD(month, @thedate, -6)

If it's the last day of the quarter before last quarter you can use the EOMONTH function to turn this date into the last day of the month rather than the first

You could also consider taking your current date, adding (((MONTH(date) - 1) % 3) - 6) months to it then EOMONTHing it. The formula produces either -4, -5 or -6 depending on whether the month is the first second or third in the quarter (7th is the first month of q3, it becomes -4, and 7-4 = 3, March, the last month of q1). EOMONTH will put the date to last in the month at the end of the math

Upvotes: 1

CR7SMS
CR7SMS

Reputation: 2584

You can give this a try:

declare @month int;declare @edate date,@year int;
set @edate= dateadd(qq, DateDiff(qq, 0, Dateadd(Month,-6,'2020-07-14'))+1,-1)
set @month=month(@edate)
set @year=year(@edate)
select @month
select @year

Upvotes: 1

Ross Bush
Ross Bush

Reputation: 15185

If it is the last day of the quarter then here is one solution. This should work going forwards and backwards.

DECLARE @ReportDate DATETIME = '2020-07-14'
DECLARE @QuarterInterval INT = -2

DECLARE @EndOfQuarter DATETIME =(SELECT DATEADD(DAY, -1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0,DATEADD(QUARTER,@QuarterInterval , @ReportDate)) +1, 0)))

SELECT 
    TheDate = @EndOfQuarter,
    TheMonth = DATEPART(MONTH,@EndOfQuarter),
    TheYear = DATEPART(YEAR,@EndOfQuarter)

Upvotes: 4

Related Questions