MySQL Built-in Functions III — Date and Time Functions

Date and time functions work on DATE, TIME and DATETIME values. They are very useful in business for working out ages, service periods, due dates and the like. MySQL stores a date in the form YYYY-MM-DD. The common functions are:

• CURDATE() (also CURRENT_DATE) — today's date; NOW() — the current date and time; CURTIME() — the current time.
• YEAR(date), MONTH(date), DAY(date) — pull out the year, month number and day number from a date. For '2022-06-01', YEAR is 2022, MONTH is 6, DAY is 1.
• MONTHNAME(date) and DAYNAME(date) — the name of the month and the weekday, e.g. 'June' and 'Wednesday'.
• DAYOFWEEK(date) — the weekday as a number (1 = Sunday ... 7 = Saturday).
• DATEDIFF(date1, date2) — the number of days between two dates (date1 - date2).
• DATE_ADD(date, INTERVAL n unit) and DATE_SUB(...) — add or subtract a period (the unit being DAY, MONTH, YEAR, etc.). DATE_ADD('2022-06-01', INTERVAL 1 YEAR) gives '2023-06-01'.

For example, to show the year in which each employee joined:

SELECT Name, YEAR(JoinDate) AS JoinYear
FROM Employee;

To work out how many days each employee has served up to today:

SELECT Name, DATEDIFF(CURDATE(), JoinDate) AS DaysServed
FROM Employee;

To list employees who joined in the year 2022:

SELECT Name, JoinDate FROM Employee
WHERE YEAR(JoinDate) = 2022;

And to find the date one year after each employee joined (say, when a probation review is due):