8 Replies Latest reply on Apr 14, 2013 7:26 PM by 1002942

# converting part of the string to a date and subtract with sysdate.

HINT! In order solve this you must know how the pnr is assembled. Study this:

650323-5510, we only need the first six characters. They inform us about when the person (car owner) was born. In this case it is 23 Mars 1965. You have to use several oracle built-in-functions to solve this. Hint! Begin by converting part of the string to a date and subtract with sysdate.

select to_char(to_date(cast(pnr,'YYMMDDMM'))) from car_owner;

please what am i doing wrong. i need the result to be something like this

Hans, Rosenboll, 59,6 years.
• ###### 1. Re: converting part of the string to a date and subtract with sysdate.
let me explain alittle

i have a column called pnr with values 650323-551

pnr
................
650323-551

i wan to convert that value to date so i can calculate the ages on the car_owners table

so, the age of this pnr 650323-551 is 23rd march 1965

how do i extract just this value 650323 from 650323-551 so i can use it in the to_date function.
• ###### 2. Re: converting part of the string to a date and subtract with sysdate.
Hi.

The main problem here is you have only last two digits of year. That could be the problem in a couple of years from now, when somebody born after 2k would get in to your database. For now if we ignore this problem the right solution would be :

<code>
SELECT months_between(trunc(SYSDATE),
to_date('19' || substr('650323-5510',
1,
6),
'YYYYMMDD')) / 12 years_old
FROM dual
</code>

Suppose you are expecting the age of the car owner as a result above code will give you that. One again notice the '19' I appended.

Best regards.
• ###### 3. Re: converting part of the string to a date and subtract with sysdate.
thanks alot gregor13

that actually helped alot, but i still have some problems:

with this

select to_date(substr(pnr,1,6), 'YYMMDD') as years_old from car_owner;

i have now been able to get the date of the pnr as

23- AUG-65
21 -feb -49

but now i need to find a way to calculate their ages, using sysdate, but that's been giving me alot of errors
• ###### 4. Re: converting part of the string to a date and subtract with sysdate.
Hi.

I do not understand your problem. The select statement I gave you does exactly this. It returns you the age of owner in years.

To put it in your context it would be :

SELECT months_between(trunc(SYSDATE), to_date(substr('19' || pnr,1,6),'YYMMDD')) / 12 years_old FROM car_owner

Best regards.

Edited by: gregor13 on Apr 14, 2013 5:06 PM
• ###### 5. Re: converting part of the string to a date and subtract with sysdate.
finally got it

thanks gregor13

u d boss
• ###### 6. Re: converting part of the string to a date and subtract with sysdate.
declare

cursor c_carowners is
select initcap(first_name), initcap(last_name), round(months_between(trunc(SYSDATE), to_date('19' || substr(pnr,1,6),'YYYYMMDD')) / 12,1) as Year_old
from car_owner;

x c_carowners%rowtype;

begin
for x in c_carowners
loop
dbms_output.put_line(x_first_name||','||x_last_name||', Age: '||x_Year_old);
end loop;
end;

please what am i doing wrong
• ###### 7. Re: converting part of the string to a date and subtract with sysdate.
Hi.

declare

cursor c_carowners is
select initcap(first_name) as first_name, initcap(last_name) as last_name, round(months_between(trunc(SYSDATE), to_date('19' || substr(pnr,1,6),'YYYYMMDD')) / 12,1) as Year_old
from car_owner;

x c_carowners%rowtype;

begin
for x in c_carowners
loop
dbms_output.put_line(x.first_name||','||x.last_name||', Age: '||x.Year_old);
end loop;
end;

Best regards.
• ###### 8. Re: converting part of the string to a date and subtract with sysdate.
thanks alot gregor13