MySQL Date and Time Exercises: Calculate the age in year

MySQL Date Time: Exercise-6 with Solution

Write a MySQL query to calculate the age in year.

Code:

-- This SQL query calculates the age based on the difference between the current year and the year of birth.

SELECT 
YEAR(CURRENT_TIMESTAMP) - -- Calculates the current year.
YEAR("1967-06-08") - -- Calculates the year from the given birthdate.
    (RIGHT(CURRENT_TIMESTAMP, 5) < -- Checks if the month and day of the current date are before the month and day of the birthdate.
RIGHT("1967-06-08", 5)) -- Extracts the month and day from the birthdate.
as age; -- Alias for the calculated age.

Explanation:

• YEAR(CURRENT_TIMESTAMP) retrieves the current year from the current timestamp.
• YEAR("1967-06-08") retrieves the year from the given birthdate '1967-06-08'.
• (RIGHT(CURRENT_TIMESTAMP, 5) < RIGHT("1967-06-08", 5)) compares the month and day of the current date with the month and day of the birthdate to determine if the birthday has already occurred in the current year.
• The difference between the current year and the birth year is adjusted based on whether the birthday has already occurred in the current year.
• This calculation gives the correct age of the person, considering both the year difference and whether the birthday has already passed in the current year.
• The result is displayed as 'age'.

Sample Output:

age
50

MySQL Code Editor:

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

Previous:Write a MySQL query to get the last day of the current year.
Next:Write a MySQL query to get the current date in the spacific format.