Home » SQL & PL/SQL » SQL & PL/SQL » date
date [message #8286] Thu, 07 August 2003 06:46 Go to next message
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 #8287 is a reply to message #8286] Thu, 07 August 2003 06:56 Go to previous messageGo to next message
Jeanne
Messages: 26
Registered: June 2003
Junior Member
Hi, Jimmy,
What is your question?
Re: date [message #8290 is a reply to message #8286] Thu, 07 August 2003 07:53 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: Connect By Prior
Next Topic: A query required to print the row as a Column
Goto Forum:
  


Current Time: Thu Mar 28 13:14:42 CDT 2024