How to select the members who has the age more than 18 (from current date) by SQL in teradata

queryteradata

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?

Best Answer

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

Related Solutions

Mysql – Query from database

Suggestions

Write it as a INNER JOIN
Change having count(mip.id > 1) to having count(mip.id) > 1
Try counting just mip table for counting

Here are those changes

select product.id,mip.usage_type,count(mip.id)
from product INNER JOIN mip
ON product.id = mip.product_id
group by product.id,mip.usage_type having count(mip.id) > 1

Give it a Try !!!

The date function used to display month name from Date column in Teradata

The full name is returned by to_char(dateCol, 'Month')

'Month' -> 'October'
'MONTH' -> 'OCTOBER'
'month' -> 'october'
'Mon' -> 'Oct'
'MON' -> 'OCT'
'mon' -> 'oct'

Best Answer

Related Solutions

Mysql – Query from database

The date function used to display month name from Date column in Teradata

Related Question