SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How can we calculate Age of employee?


How can we calculate Age of employee?

Author
Message
Saravanan_tvr
Saravanan_tvr
SSC-Addicted
SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)

Group: General Forum Members
Points: 430 Visits: 1349
Here I have used some queries but i unable to get a exact solution...
Please try else Am I Correct?

My quires:

SELECT EMP_CODE,DATE_OF_BIRTH ,
DATEDIFF(DD,DATE_OF_BIRTH,GETDATE())/365 AS AGE
FROM EMP_MST

Many Thanks!
S.saravanan
“I am a slow walker, but I never walk backwards-
Abraham Lincoln”
arun.sas
arun.sas
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1695 Visits: 3493
Hi,

Your output gives only the days,
The age may come in the format of years months and days like 34 years 10Months and 15 days

ARUN SAS
Saravanan_tvr
Saravanan_tvr
SSC-Addicted
SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)

Group: General Forum Members
Points: 430 Visits: 1349
Ok thanks for replay...

Tell me a another way to fetch the exact age of Different Date...

Many Thanks!
S.saravanan
“I am a slow walker, but I never walk backwards-
Abraham Lincoln”
t.hitendra
t.hitendra
SSC-Addicted
SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)

Group: General Forum Members
Points: 424 Visits: 405
Hi,
you can try this
select EMP_CODE,DATE_OF_BIRTH ,DATEDIFF(DD,DATE_OF_BIRTH,getdate())/365 as years,(DATEDIFF(DD,DATE_OF_BIRTH,getdate())%365)/30 as months,
DATEDIFF(DD,DATE_OF_BIRTH,getdate())%365%30 as days
from EMP_MST where accountid=4

Hitendra
t.hitendra
t.hitendra
SSC-Addicted
SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)

Group: General Forum Members
Points: 424 Visits: 405
Hi,
you can try this
select EMP_CODE,DATE_OF_BIRTH ,DATEDIFF(DD,DATE_OF_BIRTH,getdate())/365 as years,(DATEDIFF(DD,DATE_OF_BIRTH,getdate())%365)/30 as months,
DATEDIFF(DD,DATE_OF_BIRTH,getdate())%365%30 as days
from EMP_MST where accountid=4

Hitendra
t.hitendra
t.hitendra
SSC-Addicted
SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)

Group: General Forum Members
Points: 424 Visits: 405
Hi,
you can try this
select EMP_CODE,DATE_OF_BIRTH ,DATEDIFF(DD,DATE_OF_BIRTH,getdate())/365 as years,(DATEDIFF(DD,DATE_OF_BIRTH,getdate())%365)/30 as months,
DATEDIFF(DD,DATE_OF_BIRTH,getdate())%365%30 as days
from EMP_MST where accountid=4

Hitendra
arun.sas
arun.sas
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1695 Visits: 3493
t.hitendra (5/11/2009)
Hi,
you can try this
select EMP_CODE,DATE_OF_BIRTH ,DATEDIFF(DD,DATE_OF_BIRTH,getdate())/365 as years,(DATEDIFF(DD,DATE_OF_BIRTH,getdate())%365)/30 as months,
DATEDIFF(DD,DATE_OF_BIRTH,getdate())%365%30 as days
from EMP_MST where accountid=4

Hitendra


Hi,

create the function like

CREATE FUNCTION AGE_ASOF_NOW (@EMP_DOB DATETIME,@curr_dt datetime)
returns varchar(1000)
AS
BEGIN
declare @AGE_ASOF_NOW varchar(100)
declare @DDIFF int
select @DDIFF = datediff(DAY,@EMP_DOB,@curr_dt)
select @AGE_ASOF_NOW = cast((@DDIFF/365)as varchar(4))+' Years '+ cast((@DDIFF%365)/30 as varchar(3))+' Months '+ cast((@DDIFF%365)%30 as varchar(3))+' Days '
return @AGE_ASOF_NOW
END

and call this function in the select statement like

select Dbo.AGE_ASOF_NOW (DATE_OF_BIRTH,getdate())

ARUN SAS
john.arnott
john.arnott
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2028 Visits: 3059
Please be careful using ANY advice from an open forum such as this one without testing it first. The algorithms offered so far are severely lacking in accuracy. Although as schoolchildren, we learned that there are 365 days in a year, most of us would agree that because of the nature of the rules for leap years, we cannot use either 365 or 365.25 as an accurate divisor. And, of course, you don't believe that every month has 30 days.

I do appreciate the opportunity your request and the interim responses gave me to work up what I now believe to be an accurate script for returning an age in years, months and days. As has been suggested, it would probably be best implemented as a UDF (user defined function).

Oh. One more thing. Despite my confidence in this code, you must be sure of it before using it. Test it with as many weird combinations of @Today and @DOB as you can to prove out its accuracy.
Declare @DOB smallDatetime
,@Today smallDatetime
,@AgeYears int
,@AgePlusMonths int
,@AgePlusDays int
,@LastBD datetime
,@LastMonthBD datetime

--======================--
set @DoB = '20040228'
set @Today = '20080229'
--======================--

select
@AgeYears
= year(@today)-year(@dob)
- case when month(@today) < month(@dob)
or (month(@today) = month(@dob) and day(@today) < day(@dob))
then 1
else 0
end
,@LastBD = dateadd(year,@AgeYears,@Dob)
,@AgePlusMonths = datediff(month, @LastBD, @Today)
- case when month(@today) <= month(@LastBD) and day(@today) < day(@LastBD)
then 1
else 0
end
- case when month(@dob) = 2 and day(@dob) = 29
then 1
else 0
end
,@LastMonthBD = dateadd(month,@AgePlusMonths,@LastBD)
,@AgePlusDays = datediff(day, @LastMonthBD, @Today)

Select @today as today
,@DoB DoB
,@LastBD as LastBD
,@LastMonthBD as LastMonthBD
,@AgeYears AgeYears
,@AgePlusMonths As AgePlusMonths
,@AgePlusDays as AgePlusDays


Fishbarnriots
Fishbarnriots
Old Hand
Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)

Group: General Forum Members
Points: 346 Visits: 2432
declare @birthday datetime
declare @ToDate datetime
DECLARE @smonth TINYINT, @sday TINYINT, @syear SMALLINT
DECLARE @emonth TINYINT, @eday TINYINT, @eyear SMALLINT
DECLARE @months TINYINT, @days TINYINT, @years SMALLINT
DECLARE @tdate SMALLDATETIME


set @birthday = '15 feb 1969'
--set @retireage = 65
set @birthday = @birthday--getdate()

set @ToDate = getdate()--dateadd(year, +@retireage, @birthday)

--print @ToDate

SET @smonth = MONTH(@birthday)
SET @sday = DAY(@birthday)
SET @syear = YEAR(@birthday)
SET @emonth = MONTH(@ToDate)
SET @eday = DAY(@ToDate)
SET @eyear = YEAR(@ToDate)
SET @years = @eyear - @syear
SET @months = 0
SET @days = 0
IF (@emonth >= @smonth)
SET @months = @emonth - @smonth
ELSE
BEGIN
SET @years = @years - 1
SET @months = @emonth + 12 - @smonth
END
IF (@eday >= @sday)
SET @days = @eday - @sday
ELSE
BEGIN
IF (@months > 0)
SET @months = @months - 1
ELSE
BEGIN
SET @years = @years - 1
SET @months = @months + 11
END
SET @tdate = DATEADD(yy,@years,@birthday)
SET @tdate = DATEADD(m,@months,@tdate)
SET @days = DATEDIFF(d,@tdate,@ToDate)
END





print 'Accurate Age: (' +convert(varchar(11), @ToDate, 113) + ') ' +
convert(varchar(3), @years) + ' Years ' + convert(varchar(3), @months) + ' Months ' + convert(varchar(3), @days) + ' Days ' +
substring(CONVERT(VARCHAR(8),DATEADD(ss,datediff(second, @birthday, @todate),0),108), 1, 2) + ' Hours ' + substring(CONVERT(VARCHAR(8),DATEADD(ss,datediff(second, @birthday, @todate),0),108), 4, 2) + ' Minutes ' + substring(CONVERT(VARCHAR(8),DATEADD(ss,datediff(second, @birthday, @todate),0),108), 7, 2) + ' Seconds '
Nadrek
Nadrek
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1951 Visits: 2729
+1 on testing with weird dates (both date of birth and as-of dates).

You must also know your business rule for leap day babies in non-leap-years. A version of what I use (not cleaned up! Please excuse bad table names, bad capitalization, and so on) follows.

Note there is a test query and small selection of interesting test cases that can be run as required in the comments at the beginning of the function.


create function dbo.ddfn_UT_AgeInYears
(
@START_DATE datetime,
@END_DATE datetime
)
returns int
as
/*

Original code came from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462
Original name F_AGE_IN_YEARS.

Updated 20080109 PH - comments updated.
Updated 20080423 PH - comments updated.

Function ddfn_UT_AgeInYears computes Age in years.

Input parameters @START_DATE and @END_DATE
are required. If either or both parameters
are null, the function returns null.
if @START_DATE midnight is greater than
@END_DATE midnight, the function returns NULL.

i.e. SELECT dbo.ddfn_UT_AgeInYears('1988-02-29','2007-02-28')


Age is defined as the number of anniversary dates
reached or passed from @START_DATE through @END_DATE.

Age is calculated based on midnight (00:00:00.000)
of parameters @START_DATE and @END_DATE.
Time of day is not used in the calculation.

NULL is returned when the @END_DATE is earlier
than the @START_DATE.

For example, someone born 2000-02-15
would be 5 years old on 2006-02-14,
but 6 years old on 2006-02-15.

Someone born on Feb 29 would be a year
older on Feb 28 in non-leap years, but
would be a year older on Feb 29 in leap years.

Function is valid for entire range of datetime
values from 1753-01-01 00:00:00.000 to
9999-12-31 23:59:59.997.

Quick test code:
declare @strSql varchar(8000)
set @strSql = ''
SET @strSql = 'select '
+ 'DOB AS BirthDate, '
+ 'AsOfDt AS AsOfDate, '
+ 'database.dbo.ddfn_UT_AgeInYears(DOB,AsOfDt) AS UT_AgeInYears'
+ ' FROM '
+ ' (SELECT ''2004-02-29'' AS DOB, ''2004-02-28'' AS AsOfDt'
+ ' union all SELECT ''2004-02-29'' AS DOB, ''2004-02-29'' AS AsOfDt'
+ ' union all SELECT ''2004-02-29'' AS DOB, ''2004-03-01'' AS AsOfDt'
+ ' union all SELECT '''' AS DOB, '''' AS AsOfDt'
+ ' union all SELECT ''1988-02-29'' AS DOB, ''2007-02-27'' AS AsOfDt'
+ ' union all SELECT ''1988-02-29'' AS DOB, ''2007-02-28'' AS AsOfDt'
+ ' union all SELECT ''1988-02-29'' AS DOB, ''2007-03-01'' AS AsOfDt'
+ ' union all SELECT '''' AS DOB, '''' AS AsOfDt'
+ ' union all SELECT ''1988-02-29'' AS DOB, ''2008-02-28'' AS AsOfDt'
+ ' union all SELECT ''1988-02-29'' AS DOB, ''2008-02-29'' AS AsOfDt'
+ ' union all SELECT ''1988-02-29'' AS DOB, ''2008-03-01'' AS AsOfDt'
+ ') HardCodeDateList'
exec (@strSQL)


*/

begin

declare @AGE_IN_YEARS int

-- Start out by setting the times to midnight.
select @START_DATE = dateadd(dd,datediff(dd,0,@START_DATE),0),
@END_DATE = dateadd(dd,datediff(dd,0,@END_DATE),0)

if @START_DATE > @END_DATE
begin
return null
end

select
@AGE_IN_YEARS =
datediff(yy,StartDateYearStart,EndDateYearStart) +
-- Subtract 1 if anniversary date is after end date
case
when AnniversaryThisYear <= @END_DATE
then 0
else -1
end
from
(
select AnniversaryThisYear =
dateadd(yy,datediff(yy,StartDateYearStart,EndDateYearStart),@START_DATE),
StartDateYearStart,
EndDateYearStart
from
(
select StartDateYearStart =
dateadd(yy,datediff(yy,0,@START_DATE),0),
EndDateYearStart =
dateadd(yy,datediff(yy,0,@END_DATE),0)
) aa
) a

return @AGE_IN_YEARS

end



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search