Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Calculate Age from Current Date

Author  Topic 

TashiDuks
Starting Member

3 Posts

Posted - 2012-12-27 : 03:10:11
Hello Every one,

I have a table (tblEmployeeDetails) with fields (EmpID, EmpName, Designation, DateOfBirth, Age).

Now i this table i have more than 100 employee information. Now i want each and every employ age to be calculated automatically with the change of current date.

Can anyone tell me how we can calculate the Age of all employees with the change in current date without having to open the table and change the change one by one.

Thanks

Learn till you die...

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-27 : 04:36:54
1) May be use Computed Column like
Age AS DATEDIFF(yy, DateOfBirth, getdate()) in Table definition itself...

2) Second method is
UPDATE EmpDetails
SET Age = DATEDIFF(yy, DateOfBirth, getdate())





--
Chandu
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-27 : 06:36:23
If you are trying to calculate age in the conventional sense (i.e., number of completed birthday anniversaries) use the following instead of datediff.

Age AS
(
CAST(CONVERT(CHAR(8), GETDATE(), 112) AS INT)
-
CAST(CONVERT(CHAR(8), DateOfBirth, 112) AS INT)
)/10000
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-12-27 : 08:40:11
or in short
Age AS
(
CONVERT(CHAR(8), GETDATE(), 112)*1
-
CONVERT(CHAR(8), DateOfBirth, 112)
)/10000


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-12-28 : 23:32:24
You should just calculate the age, not store it in the table, since it is constantly changing. If it is more convenient, you can create a view that calculates the age, or create a calculated column. You could also implement the calculation as a function.

The key to finding age is to find the birthday for the current year, and subtract 1 from the difference in years if the current date is before the birthday this year.

Note that the code below computes the current year birthday for Feb 29 birthdays as Feb 28 for non-leap years and Feb 29 for leap years. For comparison, I have includes the calculation that sunitabeck posted that calculates the birthday for Feb 29 birthdays as Mar 1 in non-leap years.

The concept of negative age doesn't seem to have any real world meaning, so my method returns null if the date of birth is before current date, while the code sunitabeck posted does calculate a negative age.

select 
a.DOB,
a.CURR_DATE,
Age =
datediff(yy,a.DOB,a.CURR_DATE) +
case
-- Age is null when DOB before current date
when datediff(dd,a.DOB,a.CURR_DATE) < 0
then null
-- Subtract 1 if current date before birthday in current year
when datediff(dd,dateadd(yy,datediff(yy,a.DOB,a.CURR_DATE),a.DOB),a.CURR_DATE) < 0
then -1 else 0 end ,
Age_sunitabeck =
(
CAST(CONVERT(CHAR(8),a.CURR_DATE, 112) AS INT)
-
CAST(CONVERT(CHAR(8),a.DOB, 112) AS INT)
)/10000
from
( -- Test Data
select
DOB =convert(datetime,'20040407'),
CURR_DATE = convert(datetime,'20060203')
union all
select getdate(),dateadd(ms,100,getdate()) union all
select getdate(),dateadd(ms,-100,getdate()) union all
select getdate(),dateadd(dd,-1,getdate()) union all

select getdate(),dateadd(yy,10,dateadd(ms,-100,getdate())) union all
select getdate(),dateadd(yy,10,dateadd(ms,100,getdate())) union all

select '20040407','20040407' union all
select '20040407','20050406' union all
select '20040407','20050407' union all
select '20040407',NULL union all
select NULL,'20050407' union all
select NULL,NULL union all
select '20040229','20000228' union all
select '20040229','20000229' union all
select '20040229','20000301' union all
select '20040229','20010228' union all
select '20040229','20010301' union all
select '20040229','20060227' union all
select '20040229','20060228' union all
select '20040229','20060301' union all
select '20040229','20080228' union all
select '20040229','20080229' union all
select '20060205','20050204' union all
select '20060205','20050205' union all
select '20060205','20050206' union all
select '20060205','20060204' union all
select '20060205','20060205' union all
select '17530101 00:00:00.000','99991231 23:59:59.997' union all
select '19500913', getdate()
) a
order by
a.DOB,
a.CURR_DATE

Results:

DOB CURR_DATE Age Age_sunitabeck
----------------------- ----------------------- ----------- --------------
NULL NULL NULL NULL
NULL 2005-04-07 00:00:00.000 NULL NULL
1753-01-01 00:00:00.000 9999-12-31 23:59:59.997 8246 8246
1950-09-13 00:00:00.000 2012-12-28 23:27:38.650 62 62
2004-02-29 00:00:00.000 2000-02-28 00:00:00.000 NULL -4
2004-02-29 00:00:00.000 2000-02-29 00:00:00.000 NULL -4
2004-02-29 00:00:00.000 2000-03-01 00:00:00.000 NULL -3
2004-02-29 00:00:00.000 2001-02-28 00:00:00.000 NULL -3
2004-02-29 00:00:00.000 2001-03-01 00:00:00.000 NULL -2
2004-02-29 00:00:00.000 2006-02-27 00:00:00.000 1 1
2004-02-29 00:00:00.000 2006-02-28 00:00:00.000 2 1
2004-02-29 00:00:00.000 2006-03-01 00:00:00.000 2 2
2004-02-29 00:00:00.000 2008-02-28 00:00:00.000 3 3
2004-02-29 00:00:00.000 2008-02-29 00:00:00.000 4 4
2004-04-07 00:00:00.000 NULL NULL NULL
2004-04-07 00:00:00.000 2004-04-07 00:00:00.000 0 0
2004-04-07 00:00:00.000 2005-04-06 00:00:00.000 0 0
2004-04-07 00:00:00.000 2005-04-07 00:00:00.000 1 1
2004-04-07 00:00:00.000 2006-02-03 00:00:00.000 1 1
2006-02-05 00:00:00.000 2005-02-04 00:00:00.000 NULL -1
2006-02-05 00:00:00.000 2005-02-05 00:00:00.000 NULL -1
2006-02-05 00:00:00.000 2005-02-06 00:00:00.000 NULL 0
2006-02-05 00:00:00.000 2006-02-04 00:00:00.000 NULL 0
2006-02-05 00:00:00.000 2006-02-05 00:00:00.000 0 0
2012-12-28 23:27:38.650 2012-12-27 23:27:38.650 NULL 0
2012-12-28 23:27:38.650 2012-12-28 23:27:38.550 0 0
2012-12-28 23:27:38.650 2012-12-28 23:27:38.750 0 0
2012-12-28 23:27:38.650 2022-12-28 23:27:38.550 10 10
2012-12-28 23:27:38.650 2022-12-28 23:27:38.750 10 10



CODO ERGO SUM
Go to Top of Page
   

- Advertisement -