MySQL Date Functions: Syntax, Examples, and Common Uses

Updated 5 November 2026 · Standard formula, shown below

MySQL date functions let you extract parts of a date, format dates for display, compare dates, and calculate intervals. They are SQL functions—not a single function called “DATE”—and the right choice depends on whether you need a date value, a time value, or formatted text.

What MySQL Date Functions Do

MySQL provides functions for working with values stored in date and time columns, as well as strings that can be interpreted as dates. Common types include DATE for a calendar date, TIME for a time of day or duration, DATETIME for a date and time, and TIMESTAMP for a date and time that MySQL converts between the session time zone and UTC when stored or retrieved.

Some names are also data types or type-conversion functions. For example, DATE(expr) extracts the date portion from a date-time expression; it is not the same thing as the DATE column type. Functions such as CURDATE(), DATE_ADD(), and DATEDIFF() perform different tasks.

Getting the Current Date and Time

Use the current-value functions when a query needs the date or time at execution. The main options are:

CURDATE() or CURRENT_DATE() returns the current date, typically displayed as YYYY-MM-DD.
NOW() or CURRENT_TIMESTAMP() returns the current date and time.
CURTIME() or CURRENT_TIME() returns the current time.
UTC_DATE(), UTC_TIME(), and UTC_TIMESTAMP() return current values in UTC.

For example, SELECT CURDATE(), NOW(); returns a date and a date-time value. The current-time functions are generally evaluated consistently within a single statement, rather than changing from row to row as the query runs. The session time zone matters for local current-time values and for TIMESTAMP conversion; check it when results appear shifted.

Extracting and Formatting Date Parts

To retrieve a component, use functions such as YEAR(), MONTH(), DAY(), HOUR(), MINUTE(), and SECOND(). For example, SELECT YEAR(order_date), MONTH(order_date) FROM orders; returns the year and month for each order. DAYOFWEEK(), DAYOFMONTH(), and DAYOFYEAR() return day numbers using their documented conventions; for instance, DAYOFWEEK() numbers Sunday as 1 and Saturday as 7.

DATE_FORMAT(date, format) turns a date or date-time into text using format specifiers. For example:

DATE_FORMAT(order_date, '%Y-%m') produces a year-month label such as 2026-11.
DATE_FORMAT(order_date, '%W, %M %e, %Y') produces a readable weekday, month, day, and year.
TIME_FORMAT(duration, '%H:%i:%s') formats a time value.

Format specifiers are case-sensitive: %m is a two-digit month, while %M is the month name; %i is minutes. Formatting is useful for reports and display, but it returns a string. Keep dates as date or date-time values for filtering and sorting whenever possible.

Date Arithmetic and Differences

DATE_ADD() and DATE_SUB() add or subtract an interval. The interval includes a quantity and a unit:

SELECT DATE_ADD('2026-11-05', INTERVAL 7 DAY);

This returns the date seven days later. Other units include MONTH, YEAR, HOUR, and combined units such as DAY_HOUR. Month arithmetic can be surprising near month-end: if the target month has fewer days, MySQL adjusts the result to a valid date in that month.

DATEDIFF(date1, date2) returns the number of days from date2 to date1, ignoring time-of-day portions. Thus DATEDIFF('2026-11-10', '2026-11-05') is 5. For elapsed time that includes hours, minutes, or seconds, use TIMESTAMPDIFF(unit, start, end), choosing an appropriate unit. Its result is an integer in that unit, so smaller units may be needed to preserve detail.

Parsing, Conversion, and Filtering

STR_TO_DATE(string, format) parses text according to a format and returns a date or date-time value when the input matches. For example, STR_TO_DATE('05/11/2026', '%d/%m/%Y') interprets the text as 5 November 2026. Be explicit about formats: a string like 05/11/2026 is ambiguous without knowing whether the order is month-day-year or day-month-year.

Use DATE(expr) to remove the time portion from a date-time value, or CAST(expr AS DATE) to convert a compatible expression to a date. For filtering a whole day in a date-time column, a range is often preferable to wrapping the column in a function:

WHERE created_at >= '2026-11-05' AND created_at < '2026-11-06'

This includes every time on November 5 without needing to guess the last representable second. It can also let MySQL use an index on created_at more effectively than a condition such as DATE(created_at) = '2026-11-05', depending on the query and indexes.

Common Problems and Good Practices

Invalid or incomplete dates: Results and acceptance depend on the expression, SQL mode, and MySQL version. Validate input rather than assuming malformed date strings will be handled as intended.
Time-zone confusion: TIMESTAMP values are converted using the session time zone; DATETIME values are not automatically converted that way. Choose the type based on what the value represents.
Unexpected strings: DATE_FORMAT() produces text, which may sort differently from chronological date values if used as a sort key.
Boundary mistakes: A comparison using “less than or equal to midnight” can omit later times that day. A half-open range from the day’s start to the next day’s start avoids this issue.
Week numbering: Week functions have different modes and conventions. Specify the intended week system rather than assuming every database or locale numbers weeks alike.

For reliable results, store dates in a native date or date-time type, use consistent input formats, and keep presentation formatting separate from filtering and calculations. Function behavior and available features can vary across MySQL versions, so consult documentation for the version in use when relying on less common functions or edge cases.

Frequently Asked Questions

What is the MySQL function for today’s date?
CURDATE() or its synonym CURRENT_DATE() returns the current date.

How do I get the current date and time in MySQL?
Use NOW() or CURRENT_TIMESTAMP(). The value reflects the session’s time-zone context.

How do I add days to a date in MySQL?
Use DATE_ADD(date_value, INTERVAL number DAY). For example, DATE_ADD(CURDATE(), INTERVAL 10 DAY) returns the date ten days ahead.

What is the difference between DATEDIFF and TIMESTAMPDIFF?
DATEDIFF() returns a difference in days and ignores time portions. TIMESTAMPDIFF() lets you specify a unit, such as hours or minutes, and returns an integer in that unit.

How can I format a date as YYYY-MM?
Use DATE_FORMAT(date_value, '%Y-%m'). The output is text, so retain the original date value for date comparisons.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.