Get data for past three years starting from January-CodePudding

I am writing a query in which I need to get data for the past three years. What I'm looking to capture is the past three years (Jan - Dec). Unfortunately, mine is showing three year history from today's date (36 months).

Query: WHERE date_eff_dt >= DATEADD(YEAR,-3,GETDATE())

Output: https://i.stack.imgur.com/kC0G8.png

Need: Output to include January data for 2019.

CodePudding user response：

The earlier answer should work if you are looking for all data after 3 years ago. I could be misinterpreting your question but I believe you are looking for 3 full calendar years of data. I would use the following pattern:

WHERE YEAR(date_eff_dt) BETWEEN YEAR(GETDATE()) - 3 AND YEAR(GETDATE()) - 1

CodePudding user response：

The comment above showed the right way to determine the starting point:

DECLARE @start datetime 
        = DATEFROMPARTS(DATEPART(YEAR, GETDATE()) - 3, 1, 1);

The right way to query the data in the table based on this time frame - to ensure you might use any existing or future indexes on the datetime column - is with a range predicate:

WHERE date_eff_dt >= @start

The requirements were unclear, but if you only want the data within those three years and nothing that has happened this year, just add an end bound:

  AND date_eff_dt < DATEADD(YEAR, 3, @start)

Or you could flip it the other way around:

DECLARE @end datetime
        = DATEFROMPARTS(DATEPART(YEAR, GETDATE()), 1, 1);

SELECT 
  ...
WHERE date_eff_dt >= DATEADD(YEAR, -3, @end)
  AND date_eff_dt <  @end;

CodePudding user response：

try comparing year to year in the filter:

where DatePart(Year,date_eff_dt) >=DatePart(Year,GetDate())-3