0

There is a column as DATE_OF_BIRTH of datatype varchar(10). The expected data format is YYYY/MM/DD. However there are some NULL values and some values only contain the year(YYYY) value alone. The output is to calculate the AGE with this data. If the AGE is greater than 18 then the value must be retained, else it should be populated as NULL There is a sample data that is mentioned below

Input

DATE_OF_BIRTH
2000/01/01
2019/03/04
1999/02/18
?
1998
?
?

Output

DATE_OF_BIRTH
2000/01/01
?
1999/02/18
?
1998
?
?

Any help is appreciated. Thanks

Shivu
  • 11
  • 1

2 Answers2

0

You can use a case expression and SUBSTRING() to do this:

CASE WHEN EXTRACT(YEAR FROM CURRENT_DATE) - CAST(SUBSTRING(DATE_OF_BIRTH FROM 1 FOR 4) AS INT) < 18 THEN NULL ELSE DATE_OF_BIRTH END
JNevill
  • 46,980
  • 4
  • 38
  • 63
0

Assuming you need to be conservative for the "year-only" values, but want to show values on / after 18th birthday:

CASE WHEN ADD_MONTHS(CURRENT_DATE,-18*12) >= /* Most recent date to be shown */
  CAST(CASE WHEN LENGTH (DATE_OF_BIRTH) = 4 /* Special handling of year-only */
       THEN DATE_OF_BIRTH||'/12/31' /* Conservative, assume last day of year */
       ELSE DATE_OF_BIRTH END
       AS DATE FORMAT 'yyyy/mm/dd')
THEN DATE_OF_BIRTH END
Fred
  • 1,916
  • 1
  • 8
  • 16
  • 1
    This will run fine until it fails on 2024-02-29 with an *invalid date*. Better use `add_months(current_date, -12*18)`, non-Standard SQL, but working :-) – dnoeth Oct 07 '22 at 18:15
  • @dnoeth you are correct, of course. Edited by answer. – Fred Oct 07 '22 at 19:32