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

1.4K Asked by AshishSinha in SQL Server , Asked on Dec 14, 2023

I am tasked with developing a database for a system like healthcare that can store the information of patients, including their date of birth. How can I construct an SQL query for calculating the present age of every patient based on their date of birth?

Answered by Charles Parr

In the context of SQL, to calculate age from date of birth in SQL, you can easily go with the DATEIFF() function. However, there are also some other functions available in different database systems such as MySQL, PostgreSQL, etc.

Here is the example given by using the data server MySQL:-

    Patient_id,featurestefeaturesth,

    STAMPDYEAR, dte_ofbirth, CURDATE()) AAIage from 

    Patients_table;

This above SQL query will calculate the age, of patients by finding the difference in years between the “date of birth” column and the current date. Then the “TIMMPDI” function showcases the result of the age in years.

You can replace “patients _ name” and “patients_ Id” with your real name and ID of your patients.

In the SQL server, the method is a little bit different

SELECT 

    Patient_id,

    Date_of_birth,

    DATEDIFF(YEAR, date_of_birth, GETDATE()) AS age

FROM 

   A  Patients_table;

In the SQL server, the GETDATE() function helps in providing the current date in the SQL server.

Your Answer

Answer (1)

Ranjana

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.

3 Days