Search code examples
sqldateaddsnowflake-cloud-data-platform

How to select two last year in more actual year

I would like to know how we can select the two last year in more the current year.

For example, If I have a table like it :


id     date_enter      cost


1      19/09/2019    400$

1      20/09/2020    402$
     
1      19/04/2021   450$

1      19/09/2018    500$

1      05/03/2019    600$
    
1      19/09/2015    400$

Then I want it :


id     date_enter      cost

1      19/09/2019    400$

1      20/09/2020    402$
    
1      19/04/2021   450$

1      05/03/2019    600$

I tried to use dateadd like it :

date_enter>=DATEADD(year,-2,GETDATE())

But I just got the 2 last year since now, I would like to got 2019, 2020 and 2021

thanks

Solution

  • You want to get data from January 1, 2019 (i.e. all the data from 2019, 2020, and 2021). Use DATE_FROM_PARTS to construct that date.

    select * 
from mytable
where date_enter >= date_from_parts(year(current_date) - 2, 1, 1);