How to select whether or not a specific date is before X amount of years? – Presto SQL

I’m trying to select whether or not the date value in one of my columns takes place before a certain amount of years.

The code I’m trying out:

SELECT CASE
WHEN CAST(my_date AS DATE) + INTERVAL '10' YEAR < CURRENT_DATE THEN 'OVER 10 YEARS AGO'
ELSE NULL AS date_check

My expected output should be NULL for the dates I’m using (e.g. 2019-09-30) as they fall within the last 10 years, but for some reason, I’m getting OVER 10 YEARS AGO for everything.

When I test with values actually over 10 years ago (e.g. 2010-07-17), I’m also getting OVER 10 YEARS AGO as expected, however.

How is it possible to achieve what I’m trying to do? What appears to be wrong with my code?

Answer

WITH cte (my_date) AS (
          SELECT current_date - INTERVAL '11' YEAR UNION
          SELECT '2010-07-17' UNION
          SELECT '2019-09-30'
     )
SELECT CASE
          WHEN CAST(my_date AS DATE) + INTERVAL '10' YEAR < CURRENT_DATE
          THEN 'OVER 10 YEARS AGO'
          ELSE NULL
        END AS date_check
 FROM cte
;

Having said that, it’s best to perform your calculation against the literal, not your column:

WITH cte (my_date) AS (
          SELECT current_date - INTERVAL '11' YEAR UNION
          SELECT '2010-07-17' UNION
          SELECT '2019-09-30'
     )
SELECT CASE
          WHEN CAST(my_date AS DATE) < CURRENT_DATE - INTERVAL '10' YEAR
          THEN 'OVER 10 YEARS AGO'
          ELSE NULL
        END AS date_check
 FROM cte
;

and, if possible, use the correct type without CAST.

Full test case