How to subtract years from sysdate

Question

What would be the expression to check if someone's 20 years or over without hard-coding the date?

In the SQL

 SELECT student_fname 
 FROM students
 WHERE dob<'05-MAR-1995';

I was thinking about using SYSDATE but then I don't know the syntax to subtract 20 years.

Note that subtracting intervals can be problematic. If your `sysdate` is a Feb 29, subtracting a number of years will fail if the resulting year is not a leap year. Now, in this case, since you're subtracting a multiple of 4, you're safe until 2120 which is probably good enough. If you were to subtract a different interval, though, you might have issues. — Justin Cave, Mar 05 '15 at 22:22
This is a very remote posibility, but can happen, i think is better using add_months:) — Aramillo, Mar 05 '15 at 22:26
For 20 years, you can do `sysdate - 20*365.25`. This will also work until 2120. Of course, Justin's answer is better. — Gordon Linoff, Mar 05 '15 at 22:32
@Justin Cave -- When you say 'fail' do you mean error and die or just give you say the 28th instead of 29th? I'm not overly familiar with oracle I confess I've used mysql much more heavily and it just converts 29 to 28 which for all my purposes seems to be sufficient. — Uncle Iroh, Mar 05 '15 at 22:53
@UncleIroh, it fails as per the ANSI standard. If MySQL doesn't fail in such a case with an interval, it doesn't adhere to the ANSI standard. — David Faber, Mar 05 '15 at 22:59
@GordonLinoff, this gives fractional values, better use `TRUNC(sysdate - 20*365.25)`. Of course `ADD_MONTH()` is the best solution. — Wernfried Domscheit, Mar 06 '15 at 06:40

score 59 · Accepted Answer · answered Mar 05 '15 at 22:17

59

WHERE dob < add_months( trunc(sysdate), -12*20 );

would work assuming that you want to ignore the time component of sysdate.

answered Mar 05 '15 at 22:17

Justin Cave

1

I realize this post is old but can you tell me what the `*20` part means? – Mike - SMT Jul 15 '20 at 15:27
1

@Mike-SMT - The goal was to subtract 20 years. `add_months` adds (or subtracts) months so this subtracts 12*20 months to subtract 20 years. – Justin Cave Jul 15 '20 at 16:08
1

Ah I must have missed that part. – Mike - SMT Jul 15 '20 at 19:28
1

Also works for leap years! – Anton vBR Jan 03 '22 at 13:48

score 8 · Answer 2 · answered Feb 16 '21 at 10:05

8

In Oracle the NUMTOYMINTERVAL function is designed precisely for this usecase, eg

SELECT SYSDATE - NUMTOYMINTERVAL(20, 'year') FROM dual;

answered Feb 16 '21 at 10:05

Iain Hunter

2

This throws an error if SYSDATE is 29th of february whereas add_months() does not. For other purposes it is clean. – Anton vBR Jan 03 '22 at 13:48

2 Answers2