15,940,271 members
See more:
I have a database with a table called employee with the following fields name.date of birth,sex,qualification,first appointment date,grade,date of licence,date of authority,date of appointment to current position. Am new to sql statement but can do basic select statements.

Now i need a statement that will show name,age(current year-date of birth),sex,qualification,years of service( current year - date of first appointment),years at current grade(current year - date of appointment to current position)

regards
Posted
Updated 19-Jul-12 18:28pm
v2

## Solution 1

have a look at DATEDIFF[^]

i.e. age would be `DATEDIFF(year, dob, getdate())`

## Solution 2

Hi Mayeso,

Please look into the below query.

SQL
```select name, DATEDIFF(yy,dateofbirth,GETDATE())as DOB,sex,qualification,
DATEDIFF(yy,firstappointmentdate,GETDATE()) as [Year Of Services],

DATEDIFF(yy,datofappt,GETDATE()) as [years at current grade]

from #temp1```

It will surely help u .

mayeso 20-Jul-12 2:38am
thanks Arul R Ece, i have tried to implement that but am getting an error" undefined function GETDATE in expression"
Arul R Ece 20-Jul-12 2:51am
Can u please post ur query.
Because the above given query working fine
mayeso 20-Jul-12 4:37am
Arul R Ece thanks for your follow up,just executed in the wrong db, it works but on age am getting same age for all employees. May be this is because the field dateofbirth is an integer,it accepts year only like 2005
Arul R Ece 20-Jul-12 4:40am
Can u please use datetime as data type for AGE

## Solution 3

Hi Mayeso,

Please look into the below example.

I hope it will give clear solution for u

SQL
```create table #temp1
(
name varchar(10),
dateofbirth datetime,
sex varchar(10),
qualification varchar(10) ,
firstappointmentdate datetime,
dateofli datetime ,
dateofaut datetime,
datofappt  datetime)

select * from #temp1

insert into #temp1 values('arul','1989-03-23','male','BE','2010-09-21','B','2009-07-20' ,

'2009-05-20','2010-07-20')

select name, DATEDIFF(yy,dateofbirth,GETDATE())as DOB,sex,qualification,
DATEDIFF(yy,firstappointmentdate,GETDATE()) as [Year Of Services],

DATEDIFF(yy,datofappt,GETDATE()) as [years at current grade]```