database sql query for age calculation for given data

Question

0.00/5 (No votes)

See more:

i want to calculate age
i am given the date of birth and date of retirement and in some cases date of death.

the logic is as:

if given = date of death
than age = date of birth - date of death

else
age = date of birth - date of retirement

Posted 5-Jul-11 19:57pm

Db issues

Add a Solution

Comments

[no name] 6-Jul-11 2:03am

what age you want to calculate

5 solutions

Solution 2

SQL

declare @dob datetime
declare @death datetime
declare @retire datetime
declare @date datetime

set @dob = '30-JUN-1959'
set @death = null
set @retire = '20-JAN-2010'

if @death is null
    set @date = @retire
else
    set @date = @death



    select year(@date)
     - year(@DOB)
     - case when month(@DOB) > month(@date) then 1
            when month(@DOB) < month(@date) then 0
            when day(@DOB) > day(@date) then 1
            else 0
        end as age

Posted 5-Jul-11 20:16pm

_Maxxx_

Solution 3

the above query works but ,
age column is already added and i want to insert results in it so that whenever the database is to review, age column can be seen

Posted 5-Jul-11 23:11pm

Db issues

Solution 4

SQL

use [ Database]
update [Table1]
SET age=
(select case when not [Date of Death]  is null then
 datediff(year, [Birth date], [Date of Death] )
 else datediff(year , [Birth date], [Date of Retirement] )
 end from Table1)

its not working when using above code

Posted 5-Jul-11 23:22pm

Db issues

Solution 5

XML

that will make my problem more clear
let it be an employee mgmt system
i have
EID (PK)
Date of birth (datetime)
date of joining (datetime)
date of retirement(datetime)
date of death (date time)

i do not have to calculate age till today but i want to calculate age till date of retirement or date of death(if available)
 i want to declare a new column "age" and keep the records of every employee in it
This query is working for it
<pre lang="sql">select case when not death is null then
datediff(year, birth, death )
else datediff(year , birth, date_of_retirement ) end from Table_3</pre>
it works ok, but i want to save the results in "age"

Posted 6-Jul-11 20:44pm

Db issues

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)

2irfanshaikh · Accepted Answer · 2011-07-05T20:07:00

Solution 1

SQL

select case when not death is null then 
datediff(year, birth, death ) -- No of years between birth and death
else datediff(year , birth, getdate() ) -- No. of years between birth and current day
end from Table_3

Posted 5-Jul-11 20:07pm

2irfanshaikh

Updated 5-Jul-11 20:08pm

v3

Comments

Db issues 6-Jul-11 3:31am

it works, thanks, now if i want to UPDATE that figure in a column name "age" what would be the data type in sql server, and what would be the command.