0

If we are having a table named 'MEMBERS' with below details, then how to fetch the data from the table with the age 18 or more?

S.no  Name     DOB        City
1     sam     19890203   USA
2     andrew  20020304   UK
3     karun   20050827   sydney
4     virat   19870202   India
5     mrunal  19920802   India

Could anyone please help me on this?

1
  • What's the datatype of DOB, DATE or INT?
    – dnoeth
    Commented Aug 10, 2020 at 8:48

1 Answer 1

1

Simple logic is based on comparing DOB with the date 18 years before today.

If the datatype of DOB is DATE:

where DOB < add_months(current_date, -18*12)

If it's an INT:

where DOB < cast(add_months(current_date, -18*12) as int) + 19000000

If you want to show the age

trunc(months_between(current_date, DOB)/12)

or for INT

trunc(months_between(current_date, cast(DOB - 19000000 as date))/12)

+/- 19000000 is based on the internal format of a Teradata date:

(year - 1900) * 10000
+ month * 100
+ day
1
  • ADD_MONTHS subtracts 18 years from today and then this date is converted to an INT with YYYYMMDD format (based on that internal format). E.g. 2020-08-10 -> 2002-08-10 -> (2002-1900)*10000+8*100+10 -> 1020810 +19000000 -> 20200810
    – dnoeth
    Commented Aug 10, 2020 at 9:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.