How can I calculate age from date of birth in SQL?

Calculating age from a date of birth in SQL depends on the database system you’re using. Here are some common methods:

1. Using DATEDIFF() (SQL Server, MySQL)

In SQL Server:

SELECT DATEDIFF(YEAR, '1990-02-20', GETDATE()) - 
       CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, '1990-02-20', GETDATE()), '1990-02-20') > GETDATE() THEN 1 ELSE 0 END 
       AS Age;

In MySQL (before v8.0):

  SELECT TIMESTAMPDIFF(YEAR, '1990-02-20', CURDATE()) AS Age;

2. Using EXTRACT() (PostgreSQL, MySQL 8+)

PostgreSQL/MySQL 8+ allows calculating age like this:

  SELECT EXTRACT(YEAR FROM AGE(NOW(), '1990-02-20')) AS Age;

3. Using CURRENT_DATE (Oracle, PostgreSQL)

In Oracle, you can use TRUNC():

  SELECT TRUNC((SYSDATE - TO_DATE('1990-02-20', 'YYYY-MM-DD')) / 365) AS Age FROM dual;

4. Handling Leap Years

To avoid errors due to leap years, always ensure that your calculation accounts for the actual birth date before subtracting full years.

Best Practice

• Always check how your database handles date arithmetic, as some use different functions (NOW(), SYSDATE(), CURRENT_DATE).
  
• If querying large datasets, index the birthdate column for performance.