CODEWITHSUNDEEP
sqloracle-database
Ramiz Tariq
Ramiz Tariq

Reputation: 387

Check for date less than age 90 in oracle sql

I have condition in which I need Date of birth must be less than system date, not null, age less 90 years then insert into table. I don't know how to use age less than 90 in query

Query:

INSERT INTO table2 (dob)

SELECT dob
FROM table1
WHERE dob <= sysdate
AND dob IS NOT NULL;

Please tell me how to use less than age 90 in query

Thanks

Upvotes: 0

Views: 608

Answers (3)

user330315
user330315

Reputation:

I prefer working with interval constants for this kind of queries:

select *
from table1
where dob > trunc(sysdate) - interval '90' year;

There is no need to add an additional dob is not null condition because dob > ... will only return something if dob is not null.

Upvotes: 0

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59476

Try this one:

SELECT dob
FROM table1
WHERE dob > ADD_MONTHS(sysdate, -90*12)
AND dob IS NOT NULL;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521599

Here is one option:

SELECT dob
FROM table1
WHERE
    TRUNC(MONTHS_BETWEEN(sysdate, dob) / 12) < 90 AND
    dob IS NOT NULL;

Upvotes: 3

Related Questions