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() ) aorder by a.DOB, a.CURR_DATE
Results:DOB CURR_DATE Age Age_sunitabeck----------------------- ----------------------- ----------- --------------NULL NULL NULL NULLNULL 2005-04-07 00:00:00.000 NULL NULL1753-01-01 00:00:00.000 9999-12-31 23:59:59.997 8246 82461950-09-13 00:00:00.000 2012-12-28 23:27:38.650 62 622004-02-29 00:00:00.000 2000-02-28 00:00:00.000 NULL -42004-02-29 00:00:00.000 2000-02-29 00:00:00.000 NULL -42004-02-29 00:00:00.000 2000-03-01 00:00:00.000 NULL -32004-02-29 00:00:00.000 2001-02-28 00:00:00.000 NULL -32004-02-29 00:00:00.000 2001-03-01 00:00:00.000 NULL -22004-02-29 00:00:00.000 2006-02-27 00:00:00.000 1 12004-02-29 00:00:00.000 2006-02-28 00:00:00.000 2 12004-02-29 00:00:00.000 2006-03-01 00:00:00.000 2 22004-02-29 00:00:00.000 2008-02-28 00:00:00.000 3 32004-02-29 00:00:00.000 2008-02-29 00:00:00.000 4 42004-04-07 00:00:00.000 NULL NULL NULL2004-04-07 00:00:00.000 2004-04-07 00:00:00.000 0 02004-04-07 00:00:00.000 2005-04-06 00:00:00.000 0 02004-04-07 00:00:00.000 2005-04-07 00:00:00.000 1 12004-04-07 00:00:00.000 2006-02-03 00:00:00.000 1 12006-02-05 00:00:00.000 2005-02-04 00:00:00.000 NULL -12006-02-05 00:00:00.000 2005-02-05 00:00:00.000 NULL -12006-02-05 00:00:00.000 2005-02-06 00:00:00.000 NULL 02006-02-05 00:00:00.000 2006-02-04 00:00:00.000 NULL 02006-02-05 00:00:00.000 2006-02-05 00:00:00.000 0 02012-12-28 23:27:38.650 2012-12-27 23:27:38.650 NULL 02012-12-28 23:27:38.650 2012-12-28 23:27:38.550 0 02012-12-28 23:27:38.650 2012-12-28 23:27:38.750 0 02012-12-28 23:27:38.650 2022-12-28 23:27:38.550 10 102012-12-28 23:27:38.650 2022-12-28 23:27:38.750 10 10
CODO ERGO SUM