VMware ESXi and vSphere Cluster Management

MySQL Date Functions: CURDATE, DATEDIFF, DATE_ADD, and DAYNAME

Learn how to use MySQL date functions to retrieve today’s date, compare dates, add intervals, and display weekday names.

MySQL date functions manipulate temporal values, meaning date- or time-related values handled by MySQL. They help you obtain today’s date, compare dates, shift a date forward or backward, and display readable information such as a weekday name.

A DATE is a calendar date commonly written in YYYY-MM-DD format. A date literal is a date written directly in a query, such as '2016-05-07'.

Common MySQL date functions

CURDATE(): retrieve today’s date

CURDATE() returns the current date according to the MySQL database server or active session environment. The standard result format is YYYY-MM-DD.

SELECT CURDATE();

This query returns one row containing the current date. Its result changes as the calendar date changes and the query runs again.

For example, a result might look like 2026-08-18, but the actual value depends on the date recognized by the MySQL environment at execution time.

DATEDIFF(): calculate the number of days between dates

DATEDIFF(date1, date2) calculates the number of calendar days represented by date1 minus date2. It is intended for day-based differences rather than differences involving hours, minutes, or seconds.

SELECT DATEDIFF('2016-02-05', '1900-03-11') AS days;

The first date is later than the second, so this query returns a positive day count. The AS days portion creates a column alias, which is a temporary label for the result column.

Argument order controls the sign:

• A positive result means date1 is later than date2.
• A negative result means date1 is earlier than date2.
• Zero means both dates represent the same calendar date.

SELECT
  DATEDIFF('2026-08-18', '2026-08-10') AS later_minus_earlier,
  DATEDIFF('2026-08-10', '2026-08-18') AS earlier_minus_later,
  DATEDIFF('2026-08-18', '2026-08-18') AS same_date;

Use quoted date literals in valid YYYY-MM-DD format. For example, '2016-05-07' is a date literal, while an unquoted value may be interpreted incorrectly.

DATE_ADD(): add an interval to a date

DATE_ADD(date, INTERVAL value unit) returns a date after adding an interval. The INTERVAL keyword is required, followed by a number and a unit such as DAY, MONTH, or YEAR.

DATE_ADD(date_expression, INTERVAL number unit)

Add days

SELECT DATE_ADD('2016-05-07', INTERVAL 75 DAY) AS date_after_75_days;

The result is 2016-07-21, which is 75 days after the supplied date.

Add months

SELECT DATE_ADD('2016-05-07', INTERVAL 2 MONTH) AS date_after_2_months;

The result is 2016-07-07, two calendar months after the starting date.

Add years

SELECT DATE_ADD('2016-05-07', INTERVAL 3 YEAR) AS date_after_3_years;

The result is 2019-05-07, three years after the starting date.

These calculations are useful for deadlines, subscription renewals, expiration dates, reminders, and scheduled follow-up events.

DAYNAME(): display a weekday name

DAYNAME(date) returns the name of the weekday associated with a supplied date. It is useful when a report or user interface should show a readable weekday instead of only a numeric date.

SELECT DAYNAME('2016-03-10') AS weekday_name;

The result is Thursday. The date literal is quoted and uses the ISO-style YYYY-MM-DD notation.

Using date functions in SELECT queries

Each function can be called by itself in a SELECT statement. Aliases make calculated columns easier to understand.

SELECT
  CURDATE() AS today,
  DATEDIFF('2026-08-18', '2026-08-10') AS days_between,
  DATE_ADD('2026-08-18', INTERVAL 30 DAY) AS deadline,
  DAYNAME('2016-03-10') AS weekday_name;

This query produces four calculated columns: the current date, a day difference, a date 30 days after the supplied date, and a weekday name.

Date functions can also operate on table columns. For example, if an orders table has a order_date column, you could calculate a follow-up date for each row:

SELECT
  order_date,
  DATE_ADD(order_date, INTERVAL 75 DAY) AS follow_up_date,
  DAYNAME(order_date) AS order_weekday
FROM orders;

Troubleshooting date functions

DATEDIFF() returns a negative number

The dates are probably in the opposite order. Put the later date first and the earlier date second:

SELECT DATEDIFF('2026-08-18', '2026-08-10') AS days;

DATE_ADD() reports a syntax error

Check that INTERVAL, the numeric value, and the interval unit are present and ordered correctly.

SELECT DATE_ADD('2016-05-07', INTERVAL 2 MONTH);

A missing INTERVAL keyword or an incorrectly ordered expression can cause a syntax error.

A date result is invalid or unexpected

Check the supplied date literal. Use a valid date in YYYY-MM-DD format, such as '2016-05-07', and make sure the calendar date itself is valid.

CURDATE() does not match the local date

CURDATE() follows the date recognized by the MySQL session or server environment. If it differs from your local calendar date, check the active MySQL session and server time-zone settings before relying on the result.

Key points

• CURDATE() returns the current date in YYYY-MM-DD format.
• DATEDIFF(date1, date2) returns the day-based difference from the second date to the first.
• DATE_ADD(date, INTERVAL value unit) shifts a date forward by days, months, years, or another supported interval.
• DAYNAME(date) returns a readable weekday name.
• Use quoted date literals and column aliases to make SELECT queries clear.