i have this table: How do I fetch a list of Persons who have the same dob and exactly 100 years older or younger? Expected rows: The difference between dob should be 100 years Answer If the DOB is a date data type, would suggest using TRUNC and ADD_MONTHS.

SQL (Persons share the same birthday but they are exactly 100 years older or younger)

i have this table:

name     dob
Peter  1914-07-23
John   1990-03-25
Sandra 2014-07-23
Sam    1911-01-01
Jane   2011-01-01
Tom    2017-01-01

​x
 
name     dobPeter  1914-07-23John   1990-03-25Sandra 2014-07-23Sam    1911-01-01Jane   2011-01-01Tom    2017-01-01​

How do I fetch a list of Persons who have the same dob and exactly 100 years older or younger?

Expected rows:

name   dob
Peter  1914-07-23
Sandra 2014-07-23
Sam    1911-01-01
Jane   2011-01-01

 
name   dobPeter  1914-07-23Sandra 2014-07-23Sam    1911-01-01Jane   2011-01-01​

The difference between dob should be 100 years

If the DOB is a date data type, would suggest using TRUNC and ADD_MONTHS.

SELECT a.name
FROM my_table a,
     my_table b
WHERE TRUNC(a.dob) = TRUNC(ADD_MONTHS(b.dob, 100*12))

 
SELECT a.nameFROM my_table a,     my_table bWHERE TRUNC(a.dob) = TRUNC(ADD_MONTHS(b.dob, 100*12))​