SQL: Date differences are coming out as negative?

Question

My schema looks like

select dob, name from student;

DOB        NAME
---------  -------
01-APR-68  rylan
01-APR-70  joshua
01-APR-83  ava
01-APR-76  adalgisa
01-APR-83  zaida
01-APR-83  perry
01-APR-82  aiden
01-APR-75  madison
01-APR-62  zoe
01-APR-72  brendon
01-APR-75  cayden
01-APR-83  hannah

When I try to get age from date of birth as

SELECT DISTINCT s.NAME,
                s.ssn,
                s.address.streetName                                   AS street,
                ( Floor (Months_between(Trunc(sysdate), s.dob) / 12) ) AS age
FROM   student s,
       student s1
WHERE  s.dob = (SELECT Max(DISTINCT dob)
                FROM   student); 


NAME         SSN            STREET                    AGE
-----       --------      ------------                ---
perry       111110003     union square                -69

zaida       111110000     5th street                  -69

hannah      111111111     union square                -69

ava         111118888     5th street                  -69

The age is coming as negative, I have no idea why this is happening, can someone please point out the issue with the query?

Thanks

I am using Oracle Database

Your query doesn't make sense. What is the `where` clause doing? Why are you doing a self-join? — Gordon Linoff, Oct 26 '14 at 01:57
To find age check this http://stackoverflow.com/questions/17422223/oracle-query-to-calculate-current-age — Pரதீப், Oct 26 '14 at 02:08
I don't get the same results on SQL Fiddle: http://sqlfiddle.com/#!4/1b126/5/0 — Cheran Shunmugavel, Oct 26 '14 at 03:40
The self join between `student` does not make any sense and neither does the `distinct` inside the `Max()` function — , Oct 26 '14 at 07:07

score 2 · Accepted Answer · answered Oct 26 '14 at 03:11

I think that perhaps you're making this a bit more difficult than it needs to be. Try:

SELECT s.NAME,
       trunc(months_between(Trunc(sysdate), s.dob)/12) AS age
  FROM student s;

which produces the result

NAME        AGE
rylan       46
joshua      44
ava         31
adalgisa    38
zaida       31
perry       31
aiden       32
madison     39
zoe         52
brendon     42
cayden      39
hannah      31

SQLFiddle here

Share and enjoy.

SQL: Date differences are coming out as negative?

1 Answers1