Search⌘ K
Home
Courses
SQL Interview Preparation

Answer: The DATE Column and Calculations

Explore how to calculate age from a date column in SQL using functions like TIMESTAMPDIFF, DATE_FORMAT, YEAR, and DAYOFYEAR. Understand how to use SELECT, aliases, and date/time calculations to handle interview questions confidently.

We'll cover the following...

Solution

The solution is given below:

MySQL
/* The query to calculate age of an employee */
SELECT *, FLOOR(TIMESTAMPDIFF(DAY, DateOfBirth, NOW()) / 365) AS Age
FROM   Employees;

Explanation

The explanation of the solution code is given below:

  • Line 2: The SELECT query selects all the columns using * and the calculated column for age. The TIMESTAMPDIFF() function takes three variables, the unit of time (e.g., YEAR, MONTH, DAY), and two datetime expressions. It calculates the difference between the datetime expressions in the specified unit. The FLOOR() function rounds a numeric value down to the nearest integer that is less than or equal to the given value. We can also use YEAR instead of DAY in TIMESTAMPDIFF and then we ...