Date functions

The essentials

MySQL stores dates in the format 'YYYY-MM-DD' and provides functions to get the current date and time and to extract parts of a date.

Key points

• NOW(): current date and time, such as 2025-03-14 10:25:40.
• DATE(expr): extracts the date part from a date-time value. DATE('2025-03-14 10:25:40') gives 2025-03-14.
• MONTH(date): month as a number (1–12). MONTH('2024-08-15') gives 8.
• MONTHNAME(date): month name. MONTHNAME('2024-08-15') gives August.
• YEAR(date): year as a number. YEAR('2024-08-15') gives 2024.
• DAY(date): day of the month (1–31). DAY('2024-08-15') gives 15.
• DAYNAME(date): name of the weekday. DAYNAME('2024-08-15') gives Thursday.
• Date functions can be used in WHERE: SELECT * FROM Emp WHERE YEAR(DOJ) = 2020; or MONTH(DOB) = 1 to find January birthdays.
• Dates can be compared and sorted: WHERE DOJ > '2019-12-31'.

Exam tips

• Know which functions return numbers (MONTH, YEAR, DAY) and which return names (MONTHNAME, DAYNAME).
• NOW() returns a different value each time it is run, so its output in a question is shown only as a format.

Common mistakes

• Mixing DAY (day of month, a number) with DAYNAME (weekday name).
• Writing dates as 'DD-MM-YYYY' in MySQL queries.