I want to find difference between 2 dates and calculate age of the person in years
Example 1) DOB = 11/29/2001 Current Date = 1/1/2002 = 0 year 2) DOB = 11/29/2001 Current Date = 12/1/2002 = 1 year 3) DOB = 11/29/2001 Current Date = 4/1/2003 = 1 year If count of months is >12 then = 1 year If count of months > 12 but less than 24 then = 1 year
In other words only if the person has completed a full year should he be counted for that year.
While you subtract dates, the functions like day, month and year will return respective values in either days or months or years. In order to solve your problem, why do you nest the funtions; Something like this: SEL CAST(((CAST('2010-01-01' AS DATE)-CAST('2009-12-31' AS DATE)) MONTH(4)) AS INTEGER)/12 AS tst_age I think it should work for you, since you always calculating age in years only.