SQL
Calculating Age or Duration from Dates
Discover how to calculate age or the duration between two dates using SQL functions. This snippet shows how to determine the age in years from a birthdate, essential for various reporting needs.
-- For MySQL / SQL Server:
SELECT
person_name,
birth_date,
DATEDIFF(YEAR, birth_date, GETDATE()) -
CASE WHEN MONTH(birth_date) > MONTH(GETDATE()) OR
(MONTH(birth_date) = MONTH(GETDATE()) AND DAY(birth_date) > DAY(GETDATE()))
THEN 1 ELSE 0 END AS age_in_years
FROM
People;
-- For PostgreSQL (simpler syntax):
-- SELECT
-- person_name,
-- birth_date,
-- AGE(CURRENT_DATE, birth_date) AS age_interval,
-- EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth_date)) AS age_in_years
-- FROM
-- People;
How it works: This snippet provides examples for calculating a person's age in years from their birth date. The MySQL/SQL Server version uses `DATEDIFF` and conditional logic to accurately determine age, accounting for whether the birthday has passed in the current year. The commented-out PostgreSQL example demonstrates its more concise `AGE` function for similar date calculations, showing how different SQL dialects handle date arithmetic.