Oracle to_date, subtracting DDMMYY from MM-DD-YYYY

Question

I'm trying to get everyone under the age of 30 in an SQL-query. The birth date I have in my database is in the format DDMMYY and I've tried to subtract that from SYSDATE like this (where 10957 is the number of days in 30 years):

 ((TO_DATE(SYSDATE, 'MM-DD-YYYY') 
  - TO_DATE(BIRTH_DATE_FROM_DB, 'DDMMYY'), 'MM-DD-YYYY')) < 10957

It's obviously a problem to subtract a date with no information on century with one that has. This is the birth date part of a social security number, so I'm able to figure out whether it's the 1900s or 2000s based on another part of the number. Is there a way to insert the extra YY based on this?

Here is the code I use to determine century (everybody with a social security number in Norway starting with 500 or more is born after 2000):

(SUBSTR(PERSON_NR, 1, 3) > 499
AND LENGTH(PERSON_NR) = 5))

I understand that this is a complicated question, and maybe hard to give an exact answer, but I appreciate if you could point me in the right direction.

use the year format RR on birth_date_from_db. By which I mean TO_DATE(BIRTH_DATE_FROM_DB, 'DDMMRR') — Christian Palmer, Jan 09 '18 at 14:08
and for the date comparison you can use : MONTHS_BETWEEN(sysdate, TO_DATE(BIRTH_DATE_FROM_DB, 'DDMMRR')) < (12*30) — Christian Palmer, Jan 09 '18 at 14:14
`sysdate` is already a date so you should not use `to_date(sysdate)` any more than you would use `to_number(123)` or `to_char('cheese')`. If you want to round it, use `trunc(sysdate)`. — William Robertson, Jan 09 '18 at 15:15
[Possibly related](https://stackoverflow.com/q/47183794/266304), though the rules seem a bit more complicated in that version... — Alex Poole, Jan 09 '18 at 19:52

score 2 · Answer 1 · answered Jan 09 '18 at 14:44

2

Use case when and concatenate strings:

with t (person, person_nr, birth_date_from_db) as (
    select 'Mark', '501ABC', '300403' from dual union all
    select 'Paul', '417PQR', '300403' from dual )
-- end of test data    
select person, person_nr,
       months_between(
         trunc(sysdate),
         to_date(substr(birth_date_from_db, 1, 4)
           || case when substr(person_nr, 1, 3) > 499 then '20' else '19' end
           || substr(birth_date_from_db, 5, 2), 'DDMMYYYY')) / 12 as age
  from t

Mark is 14 years old, Paul 114:

PERSON PERSON_NR        AGE
------ --------- ----------
Mark   501ABC    14.6935483
Paul   417PQR    114.693548

answered Jan 09 '18 at 14:44

Ponder Stibbons

14,723
2
21
24

Thanks. This seems to be the right solution, but I'm having problems running it as a WHERE clause. I get the error ORA-01847 day of month must be between 1 and last day of month. This seems to be the same problem: https://stackoverflow.com/questions/22439654/ora-01847-day-of-month-must-be-between-1-and-last-day-of-month-but-data-is-ok – Thomas Tallaksen Jan 10 '18 at 08:53
1

@ThomasTallaksen - are you seeing [the D-number issue mentioned on this answer](https://stackoverflow.com/a/47185033/266304) perhaps? I suspect you need to fully follow the SSN rules, not just rely on the three-digit individual number. – Alex Poole Jan 10 '18 at 09:45
This definitely pointed me in the right direction. But as @AlexPoole points out, Norwegian social security numbers are more complicated than I thought initially. – Thomas Tallaksen Jan 10 '18 at 10:36

Alex Poole · Accepted Answer · 2018-01-10T10:19:36.490

The Norwegian identification number is more complicated than you suggest, as touched on in this question. You can adapt the answer from that; using a CTE to generate the same dummy data on that answer plus a couple of D numbers (and without the unhelpful future-dated value):

with t42 (ssn) as (
  select '12104900000' from dual
  union all select '12105099999' from dual
  union all select '01010000001' from dual
  union all select '02029949902' from dual
  union all select '03035450003' from dual
  union all select '04049974904' from dual
  union all select '05050050005' from dual
  union all select '07074090007' from dual
  union all select '08089999908' from dual
  union all select '01121799908' from dual
  union all select '48089999908' from dual
  union all select '52104900000' from dual
)
select ssn
from t42
where months_between(trunc(sysdate), to_date(
      case
        when to_number(substr(ssn, 1, 2)) > 31
          then to_char(to_number(substr(ssn, 1, 2)) - 40, 'FM00')
        else substr(ssn, 1, 2)
      end
    || substr(ssn, 3, 2)
    || case
        when to_number(substr(ssn, 7, 3)) between 0 and 499
          and to_number(substr(ssn, 5, 2)) between 0 and 99 then '19'
        when to_number(substr(ssn, 7, 3)) between 500 and 749
          and to_number(substr(ssn, 5, 2)) between 54 and 99 then '18'
        when to_number(substr(ssn, 7, 3)) between 500 and 999
          and to_number(substr(ssn, 5, 2)) between 0 and 39 then '20'
        when to_number(substr(ssn, 7, 3)) between 900 and 999
          and to_number(substr(ssn, 5, 2)) between 40 and 99 then '19'
      end
    || substr(ssn, 5, 2), 'DDMMYYYY')
  ) < 360;

which gets five of the twelve dummy values back:

SSN        
-----------
02029949902
05050050005
08089999908
01121799908
48089999908

Or with the converted dates and ages:

with ...
select ssn, to_char(dob, 'YYYY-MM-DD') as dob,
  trunc(months_between(trunc(sysdate), dob)) as age_in_months,
  trunc(months_between(trunc(sysdate), dob)/12) as age_in_years
from (
  select ssn,
    to_date(
      case
        when to_number(substr(ssn, 1, 2)) > 31
          then to_char(to_number(substr(ssn, 1, 2)) - 40, 'FM00')
        else substr(ssn, 1, 2)
      end
    || substr(ssn, 3, 2)
    || case
        when to_number(substr(ssn, 7, 3)) between 0 and 499
          and to_number(substr(ssn, 5, 2)) between 0 and 99 then '19'
        when to_number(substr(ssn, 7, 3)) between 500 and 749
          and to_number(substr(ssn, 5, 2)) between 54 and 99 then '18'
        when to_number(substr(ssn, 7, 3)) between 500 and 999
          and to_number(substr(ssn, 5, 2)) between 0 and 39 then '20'
        when to_number(substr(ssn, 7, 3)) between 900 and 999
          and to_number(substr(ssn, 5, 2)) between 40 and 99 then '19'
      end
    || substr(ssn, 5, 2), 'DDMMYYYY') as dob
  from t42
)
where months_between(trunc(sysdate), dob) < 360;

SSN         DOB        AGE_IN_MONTHS AGE_IN_YEARS
----------- ---------- ------------- ------------
02029949902 1999-02-02           227           18
05050050005 2000-05-05           212           17
08089999908 1999-08-08           221           18
01121799908 2017-12-01             1            0
48089999908 1999-08-08           221           18

Thanks! You're perfectly right. It works when I rewrite your code to take into account that the SSN is split in two different columns for birth date and number in my database. Thanks for a thorough answer. Stack Overflow is awesome, people are so helpful! :) — Thomas Tallaksen, Jan 10 '18 at 10:32

Oracle to_date, subtracting DDMMYY from MM-DD-YYYY

2 Answers2