Click here to Skip to main content
15,916,683 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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

have a look at DATEDIFF[^]

i.e. age would be DATEDIFF(year, dob, getdate())
 
Share this answer
 
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 .
 
Share this answer
 
Comments
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
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,
 grade varchar,
 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]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900