Using Date Functions for Age Calculation in SQL

Using Date Functions Across Databases

Write a SQL query to calculate the age of employees based on their birthdate, comparing syntax between MySQL, PostgreSQL, and Oracle.

Solution:

-- MySQL
SELECT Name, TIMESTAMPDIFF(YEAR, BirthDate, CURDATE()) AS Age
FROM Employees;

-- PostgreSQL
SELECT Name, EXTRACT(YEAR FROM AGE(BirthDate)) AS Age
FROM Employees;

-- Oracle
SELECT Name, FLOOR(MONTHS_BETWEEN(SYSDATE, BirthDate) / 12) AS Age
FROM Employees;

Explanation:

• Purpose of the Query :
• The goal is to demonstrate how date calculations differ across database systems.
• Key Components :
• TIMESTAMPDIFF (MySQL): Calculates the difference in years.
• AGE (PostgreSQL): Computes the interval between dates.
• MONTHS_BETWEEN (Oracle): Calculates months and converts to years.
• Why Compare Date Functions?:
• Date handling varies significantly across platforms, impacting query portability.
• Understanding these differences ensures accurate results.
• Real-World Application :
• In HR systems, calculating age supports compliance and reporting.

Additional Notes:

• Use ANSI SQL date functions where possible for compatibility.
• Test queries on all target platforms to ensure accuracy.
• Important Considerations:
• Account for leap years and time zones when working with dates.

For more Practice: Solve these Related Problems:

• Write a SQL query to calculate the number of days between the order date and delivery date, comparing syntax between MySQL, PostgreSQL, and Oracle.
• Write a SQL query to calculate the number of months an employee has worked, comparing syntax between MySQL, PostgreSQL, and Oracle.
• Write a SQL query to calculate the difference in years between the current date and a project start date, comparing syntax between MySQL, PostgreSQL, and Oracle.
• Write a SQL query to calculate the age of a product based on its manufacturing date, comparing syntax between MySQL, PostgreSQL, and Oracle.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise:Using Date Functions Across Databases.
Next SQL Exercise: Implementing Pagination in MySQL and SQL Server.