date [message #8286] |
Thu, 07 August 2003 06:46 |
jamesjoseph
Messages: 2 Registered: August 2003
|
Junior Member |
|
|
The table created have two cols with names DOB & DOJ in which the date of birth and date of joining is given.
Want to find out the age of the employees. in a single query.
|
|
|
|
Re: date [message #8290 is a reply to message #8286] |
Thu, 07 August 2003 07:53 |
Art Metzer
Messages: 2480 Registered: December 2002
|
Senior Member |
|
|
SQL> CREATE TABLE dates (date_of_birth DATE, date_of_joining DATE);
Table created.
SQL> INSERT INTO dates VALUES (TO_DATE('19671213','YYYYMMDD'),TO_DATE('20030201','YYYYMMDD'));
1 row created.
SQL> INSERT INTO dates VALUES (TO_DATE('19550424','YYYYMMDD'),TO_DATE('20030807','YYYYMMDD'));
1 row created.
SQL> INSERT INTO dates VALUES (TO_DATE('19600229','YYYYMMDD'),TO_DATE('20030301','YYYYMMDD'));
1 row created.
SQL> COMMIT;
Commit complete.
SQL> <font color=blue>SELECT TO_CHAR(d.date_of_birth,'fmMM/DD/YYYY') dob</font>
2 <font color=blue>, TO_CHAR(d.date_of_joining,'fmMM/DD/YYYY') doj</font>
3 <font color=blue>, TRUNC(MONTHS_BETWEEN(d.date_of_joining</font>
4 <font color=blue> , d.date_of_birth)/12) age_at_join</font>
5 <font color=blue>FROM dates d</font>
6 /
DOB DOJ AGE_AT_JOIN
---------- ---------- -----------
12/13/1967 2/1/2003 35
4/24/1955 8/7/2003 48
2/29/1960 3/1/2003 43
SQL> Good luck,
A.
|
|
|
Re: date [message #8291 is a reply to message #8286] |
Thu, 07 August 2003 07:59 |
William Robertson
Messages: 1643 Registered: August 2003 Location: London, UK
|
Senior Member |
|
|
DOJ - DOB gives interval as a number of days.
MONTHS_BETWEEN(DOJ,DOB) gives interval in months.
In 9i you can use the new INTERVAL and TIMESTAMP datatypes.
|
|
|