CODEWITHSUNDEEP
sqlsql-server
user1296762
user1296762

Reputation: 512

Records from the last 2 years - complete months

I've got code to pull back data up to the last day of the last month but I want records from the past 2 years complete month i.e. March 2013 - March 2015 so I need code to limit from 1st March 2013

This is what I have so far 

select * from table where Date <= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

Upvotes: 0

Views: 69

Answers (1)

ughai
ughai

Reputation: 9890

Based on the current date passed in @CurrentDate , You can get the last date of the previous month and the date 2 year ago based on the sql below

DECLARE @CurrentDate DATE = CURRENT_TIMESTAMP
DECLARE @LastMonthEOMDate DATE
DECLARE @TwoYearOldDate DATE

SET @LastMonthEOMDate = DATEADD(DAY,-DATEPART(day,@CurrentDate),@CurrentDate)
SET @TwoYearOldDate = DATEADD(YEAR,-2,@LastMonthEOMDate)

SELECT @CurrentDate,@LastMonthEOMDate,@TwoYearOldDate


select * from table where Date >= @TwoYearOldDate AND Date <= @LastMonthEOMDate

Upvotes: 2

Related Questions