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.
What is the difficulty level of this exercise?
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics