Menu
In a SQL Server database, I record people's date of birth. Is there an straight-forward method of working out the person's age on a given date using SQL only?
Using DATEDIFF(YEAR, DateOfBirth, GETDATE()) does not work as this only looks at the year part of the date. For example DATEDIFF(YEAR, '31 December 2007', '01 January 2008') returns 1.
869
Age of a Person = Given date - Date of birth. Ron's Date of Birth = July 25, 1985. Given date = January 28, 2021. Years' Difference = 2020 - 1985 = 35 years.
SELECT TRUNC((SYSDATE - TO_DATE(DOB, 'YYYY-MM-DD'))/ 365.25) AS AGE_TODAY FROM DUAL; This is easy and straight to the point.
The AGE function returns a numeric value that represents the number of full years, full months, and full days between the current timestamp and the argument. AGE ( expression ) The schema is SYSIBM.
How can i do this with SQL? Using year and month DATEDIFF (uses end of year/month boundaries) gives row 1 = 2 years/29 months, row 2 = 1 year/23 months.
Check out this article: How to calculate age of a person using SQL codes
Here is the code from the article:
DECLARE @BirthDate DATETIME
DECLARE @CurrentDate DATETIME
SELECT @CurrentDate = '20070210', @BirthDate = '19790519'
SELECT DATEDIFF(YY, @BirthDate, @CurrentDate) - CASE WHEN( (MONTH(@BirthDate)*100 + DAY(@BirthDate)) > (MONTH(@CurrentDate)*100 + DAY(@CurrentDate)) ) THEN 1 ELSE 0 END
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
