?
Solved

How to create an age function?

Posted on 2003-04-01
4
Medium Priority
?
844 Views
Last Modified: 2011-09-20
I have created a table Employee with the following attributes:

-FirstName varchar2(20)
-LastName varchar2(20)
-BirthDate date


I'd like to create a function that generates the age of the employee by using the BirthDate, but my knowledge is very poor when it comes to sql.

I'm using Oracle 9i.

I hope that someone knows how to help me.

 
0
Comment
Question by:SEWang
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 1

Accepted Solution

by:
suzieqt earned 225 total points
ID: 8244713
subtract the birthdate from current date (sysdate) and then use months_between to get the months.

Divide by twelve to get the years:

select months_between(sysdate,birthdate)/12
from employee

If you want months as well:

select mod(months_between(sysdate,birthdate),12)
from employee

Suzanne
0
 
LVL 1

Expert Comment

by:suzieqt
ID: 8244716
p.s. use trunc(...) to round the years figure down
0
 
LVL 7

Expert Comment

by:Bigfam5
ID: 8244768
Adding to what suziegt has, your function can be as simple as

 create or replace function age
          (v_dob date) return number is
 begin
  return trunc( months_between(sysdate, v_dob )/12);
 end;

The your select would be

select age(dob) from table;

0
 

Author Comment

by:SEWang
ID: 8244968
Thanx to the both of you!
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question