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

Question

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 1

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