examples of calculating age from date of birth in Oracle SQL:
- Calculate age in years:
SELECT
name,
birthdate,
TRUNC(MONTHS_BETWEEN(SYSDATE, birthdate) / 12) AS age_in_years
FROM
users;
This query uses the MONTHS_BETWEEN function to calculate the number of months between the birthdate and the current date and then divides that by 12 and rounds down using the TRUNC function to get the age in years.
OUTPUT:-
NAME BIRTHDATE AGE_IN_YEARS
-------------------------------------
John 1990-05-15 31
Jane 1985-08-23 36
Bob 1995-11-02 26
2. Calculate age in months:
SELECT
name,
birthdate,
MONTHS_BETWEEN(SYSDATE, birthdate) AS age_in_months
FROM
users;
This query uses the MONTHS_BETWEEN function to calculate the months between birthdates and current dates, giving us the age in months.
OUTPUT:-
NAME BIRTHDATE AGE_IN_MONTHS
-------------------------------------
John 1990-05-15 372
Jane 1985-08-23 442
Bob 1995-11-02 309
3. Calculate age in days:
SELECT
name,
birthdate,
TRUNC(SYSDATE - birthdate) AS age_in_days
FROM
users;
This query subtracts the birthdate from the current date using the - operator, which gives us the age in days. The TRUNC function removes the fractional part of the result and returns an integer value.
OUTPUT:-
NAME BIRTHDATE AGE_IN_DAYS
----------------------------------
John 1990-05-15 11335
Jane 1985-08-23 13308
Bob 1995-11-02 9462
Note that in all of these examples, users are the name of the table containing the name and birthdate columns. Also, be sure to replace SYSDATE with the current date if you're testing these queries outside of a database environment.