SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Calculate Age from Current Date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

TashiDuks
Starting Member

Bhutan
3 Posts

Posted - 12/27/2012 :  03:10:11  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 12/27/2012 :  04:36:54  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/27/2012 :  06:36:23  Show Profile  Reply with Quote
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

India
22754 Posts

Posted - 12/27/2012 :  08:40:11  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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)

USA
7020 Posts

Posted - 12/28/2012 :  23:32:24  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000