sql select statement

Question

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 19-Jul-12 18:24pm

mayeso

Updated 19-Jul-12 18:28pm

Prasad_Kulkarni

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

barneyman · Answer 1 · 2012-07-19T18:49:00

Solution 1

have a look at DATEDIFF[^]

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

Posted 19-Jul-12 18:49pm

barneyman

Arul R Ece · Answer 2 · 2012-07-19T19:18:00

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 .

Posted 19-Jul-12 19:18pm

Arul R Ece

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

Arul R Ece · Answer 3 · 2012-07-19T22:41:00

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]