Home » SQL & PL/SQL » SQL & PL/SQL » query to get year,month and days from dob (9i)
query to get year,month and days from dob [message #436665] Fri, 25 December 2009 00:30 Go to next message
shaz
Messages: 182
Registered: June 2009
Senior Member
I want to calculate the age in years to seconds.
please consider this dummy table:

  CREATE TABLE TEST_DOB
(ID NUMBER primary key not null,
dob date);

insert into TEST_DOB values(1,to_date('19960917 16:59:45','YYYYMMDD hh24:mi:ss'));
insert into TEST_DOB values(2,to_date('20020415 01:35:26','YYYYMMDD hh24:mi:ss'));
insert into TEST_DOB values(3,to_date('19901231 05:33:22','YYYYMMDD hh24:mi:ss'));
insert into TEST_DOB values(4,to_date('19950212 12:01:00','YYYYMMDD hh24:mi:ss'));
insert into TEST_DOB values(5,to_date('19980212 18:18:36','YYYYMMDD hh24:mi:ss'));



i have tried this:
SELECT a.id,a.dob,
Extract(YEAR FROM (SYSDATE - dob) year to month)||' years '||
Extract(MONTH FROM (SYSDATE - dob) year to month)||' Months ('||
Extract(day FROM (SYSDATE - dob) day to second)||' Days '||
Extract(hour FROM (SYSDATE - dob) day to second)||' Hours '||
Extract(minute FROM (SYSDATE - dob) day to second)||' Minutes '||
Extract(SECOND FROM (SYSDATE - dob) day to second)||' Seconds)' Extract 
FROM test_dob a  order by 2

[b]it gives result:[/b]
dob                     extract
12/31/1990 5:33:22 AM	19 years 0 Months (6934 Days 5 Hours 2 Minutes 1 Seconds)
2/12/1995 12:01:00 PM	14 years 10 Months (5429 Days 22 Hours 34 Minutes 23 Seconds)
9/17/1996 4:59:45 PM	13 years 3 Months (4846 Days 17 Hours 35 Minutes 38 Seconds)
2/12/1998 6:18:36 PM	11 years 10 Months (4333 Days 16 Hours 16 Minutes 47 Seconds)
4/15/2002 1:35:26 AM	7 years 8 Months (2811 Days 8 Hours 59 Minutes 57 Seconds)



i want the days should not come the total days, but the remaining days after distribution of days in years and months.
Please suggest a solution
thanks in anticipation.
Re: query to get year,month and days from dob [message #436666 is a reply to message #436665] Fri, 25 December 2009 00:43 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
>i want the days should not come the total days, but the remaining days after distribution of days in years and months.

how do you calculate "the remaining days after distribution of days in years and months."?
Re: query to get year,month and days from dob [message #436674 is a reply to message #436666] Fri, 25 December 2009 02:45 Go to previous messageGo to next message
shaz
Messages: 182
Registered: June 2009
Senior Member
what i mean to say is :
suppose a child was born on 24 dec, 2008.
then the day difference from dob and sysdate is : 366
now i want that result should come:
1 year 0 month and 1 day instead of 1 year 0 month and 366 days.
Re: query to get year,month and days from dob [message #436678 is a reply to message #436665] Fri, 25 December 2009 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How to get the difference between 2 dates in format years/months/days
I hope you will able to extend it to hours, minutes and seconds.

Regards
Michel
Re: query to get year,month and days from dob [message #436695 is a reply to message #436678] Fri, 25 December 2009 13:53 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
http://www.orafaq.com/wiki/SQL_FAQ


Quote:

How to get the difference between 2 dates in format years/months/days
I hope you will able to extend it to hours, minutes and seconds.

Regards
Michel



Requires to be a member of the forum dba-village.com to access the link

[Updated on: Fri, 25 December 2009 13:56]

Report message to a moderator

Re: query to get year,month and days from dob [message #436709 is a reply to message #436695] Sat, 26 December 2009 02:02 Go to previous message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So just register.
If one is able to do it for OraFAQ, he/she is able to do it for dba-village.

Regards
Michel

[Updated on: Sat, 26 December 2009 02:04]

Report message to a moderator

Previous Topic: Serial And Parallel Direct-Path Insert
Next Topic: Update Table with Other Table
Goto Forum:
  


Current Time: Fri Dec 02 12:05:53 CST 2016

Total time taken to generate the page: 0.16353 seconds