MySQL online course

MySQL Date Functions: Current Dates, Date Differences, Date Arithmetic, and Weekday Names

Learn MySQL date functions including CURDATE(), DATEDIFF(), DATE_ADD(), and DAYNAME() with practical SQL examples and troubleshooting tips.

MySQL date functions operate on temporal values: values that represent a calendar date, a time, or both. They help you retrieve information from dates, compare dates, and calculate new dates.

A DATE value contains a calendar year, month, and day. MySQL commonly displays it in YYYY-MM-DD format. A date literal is a quoted SQL value supplied where a date is expected, such as '2016-05-07'.

This lesson uses basic SELECT expressions and column aliases. For background, review querying a database, column aliases, and MySQL data types.

Common MySQL date functions

Getting the current date with CURDATE()

CURDATE() returns the database server's current calendar date. It takes no arguments and normally returns a DATE-style value in YYYY-MM-DD form.

SELECT CURDATE() AS current_date;

The result is evaluated using the server and session date-and-time context. Therefore, it may not match the local date on your computer if the MySQL server or session uses another time zone.

A common use is to report how long customers have been registered. In this example, signup_date is a DATE column:

SELECT
    customer_id,
    DATEDIFF(CURDATE(), signup_date) AS days_since_signup
FROM customers;

Calculating day differences with DATEDIFF()

DATEDIFF(end_date, start_date) returns the difference between two date expressions in whole calendar days. MySQL subtracts the second argument from the first:

DATEDIFF(end_date, start_date)

Use a column alias such as days to give the calculated result a readable name.

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

Because 2016-02-05 occurs after 1900-03-11, this returns 42334. Reversing the arguments returns a negative value:

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

DATEDIFF() reports calendar-day boundaries. It does not return a duration containing hours, minutes, and seconds. If your values include times and you need a precise elapsed duration, use an appropriate date-and-time function instead of treating the result as an hour-based measurement.

One practical filtering example is finding customers who signed up at least 30 days ago:

SELECT customer_id, signup_date
FROM customers
WHERE DATEDIFF(CURDATE(), signup_date) >= 30;

Adding dates with DATE_ADD()

DATE_ADD() calculates a new date by adding an interval to a date or datetime value. Its general form is:

DATE_ADD(date_value, INTERVAL quantity unit)

INTERVAL introduces the amount of time to add. The quantity is numeric, and the unit describes that quantity. This lesson uses the DAY, MONTH, and YEAR units.

Adding days

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

The result is 2016-07-21.

Adding months

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

The result is 2016-07-07.

Adding years

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

The result is 2019-05-07.

Month and year arithmetic follows calendar rules. Months do not all have the same number of days, and leap years add an extra day to February. When a target month cannot contain the original day number, MySQL normalizes the resulting date to a valid calendar date. Test month-end and leap-year cases explicitly when exact due dates matter.

In a table query, the date can come from a column rather than a hard-coded literal. For example, this creates a due date 30 days after each invoice date:

SELECT
    invoice_id,
    DATE_ADD(invoice_date, INTERVAL 30 DAY) AS due_date
FROM invoices;

Returning weekday names with DAYNAME()

DAYNAME(date_value) returns the textual weekday label represented by a date. It is useful for display and reports.

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

The result is Thursday. The function returns text, not a numeric weekday position, so it is suitable for labels but not for numeric sorting or arithmetic.

You can also use a date column to label rows in a report:

SELECT
    invoice_id,
    invoice_date,
    DAYNAME(invoice_date) AS weekday
FROM invoices;

Using date functions together

Date functions can be combined in one query. This example shows the signup date, the number of days since signup, and a date 30 days after signup:

SELECT
    customer_id,
    signup_date,
    DATEDIFF(CURDATE(), signup_date) AS days_since_signup,
    DATE_ADD(signup_date, INTERVAL 30 DAY) AS thirty_day_date
FROM customers;

Each expression has a separate job: CURDATE() supplies the current date, DATEDIFF() compares dates, and DATE_ADD() calculates a new date.

Troubleshooting date queries

DATEDIFF() returns a negative number

The arguments are probably reversed. Put the later date first when you want a positive number of days:

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

A date calculation returns NULL

One of the inputs may be NULL or may not be a valid date value. Check source columns and use valid YYYY-MM-DD values. If a table column can be NULL, decide whether the query should exclude that row or provide a replacement value.

DATE_ADD() syntax fails

Check that the expression includes the INTERVAL keyword, a numeric quantity, and a valid unit:

DATE_ADD(date_value, INTERVAL quantity DAY)

For the units covered here, use DAY, MONTH, or YEAR.

Adding months produces an unexpected end-of-month date

The target month may have fewer days than the source month. This is normal calendar normalization behavior. Test dates near the end of a month and dates around February in leap and non-leap years.

DAYNAME() is not suitable for numeric ordering

DAYNAME() returns a weekday name as text. Use it for display, and choose a numeric weekday function when you need numeric ordering or calculations.

CURDATE() does not match the local date

The MySQL server or current session may use a different time zone from the user. Check the session and server time-zone settings before using CURDATE() in time-sensitive workflows.

Exam-relevant points

• CURDATE() returns the current date and requires no arguments.
• DATEDIFF(end_date, start_date) returns whole calendar days, with the first argument minus the second.
• Reversing DATEDIFF() arguments changes a positive result to a negative one.
• DATE_ADD() uses the structure DATE_ADD(date_value, INTERVAL quantity unit).
• DAYNAME() returns a textual weekday name such as Thursday.
• Use readable aliases such as days, future_date, and weekday for calculated columns.
• Quoted date literals should use the YYYY-MM-DD format.