MySQL Date Functions: NOW, CURDATE, DATE

The current date and time

query.sql

SQL

SELECT NOW() IS NOT NULL AS now_works,
       CURDATE() = CURRENT_DATE AS curdate_is_current_date,
       CURTIME() IS NOT NULL AS curtime_works,
       UTC_TIMESTAMP() IS NOT NULL AS utc_works,
       SYSDATE() IS NOT NULL AS sysdate_works;

Output

OUTPUT

+-----------+-------------------------+---------------+-----------+---------------+
| now_works | curdate_is_current_date | curtime_works | utc_works | sysdate_works |
+-----------+-------------------------+---------------+-----------+---------------+
|         1 |                       1 |             1 |         1 |             1 |
+-----------+-------------------------+---------------+-----------+---------------+
1 row in set

Function	Returns
`NOW()` / `CURRENT_TIMESTAMP`	Date and time, fixed at the start of the statement
`CURDATE()` / `CURRENT_DATE`	Today’s date
`CURTIME()`	The current time
`UTC_TIMESTAMP()`	Date and time in UTC
`SYSDATE()`	The real time at the moment of the call (changes inside one statement)

NOW() returns the same value everywhere in one statement, which is usually what you want. SYSDATE() does not, and it also disables some optimisations, so avoid it. These functions use the session time zone (DATETIME vs TIMESTAMP).

Because the current time changes, the examples below use fixed dates, so the results are repeatable.

Parts of a date

query.sql

SQL

SELECT YEAR('2026-07-15')      AS year_part,
       MONTH('2026-07-15')     AS month_part,
       DAY('2026-07-15')       AS day_part,
       DAYNAME('2026-07-15')   AS weekday_name,
       MONTHNAME('2026-07-15') AS month_name,
       DAYOFWEEK('2026-07-15') AS dow_sunday_is_1,
       WEEKDAY('2026-07-15')   AS weekday_monday_is_0,
       DAYOFYEAR('2026-07-15') AS day_of_year,
       QUARTER('2026-07-15')   AS quarter_number,
       HOUR('2026-07-15 14:30:45') AS hour_part;

Output

OUTPUT

+-----------+------------+----------+--------------+------------+-----------------+---------------------+-------------+----------------+-----------+
| year_part | month_part | day_part | weekday_name | month_name | dow_sunday_is_1 | weekday_monday_is_0 | day_of_year | quarter_number | hour_part |
+-----------+------------+----------+--------------+------------+-----------------+---------------------+-------------+----------------+-----------+
|      2026 |          7 |       15 | Wednesday    | July       |               4 |                   2 |         196 |              3 |        14 |
+-----------+------------+----------+--------------+------------+-----------------+---------------------+-------------+----------------+-----------+
1 row in set

DAYOFWEEK counts Sunday as 1. WEEKDAY counts Monday as 0. They are different on purpose, so check which one you need.

EXTRACT: the standard way

query.sql

SQL

SELECT EXTRACT(YEAR FROM '2026-07-15') AS year_part,
       EXTRACT(MONTH FROM '2026-07-15') AS month_part,
       EXTRACT(YEAR_MONTH FROM '2026-07-15') AS year_month_value,
       EXTRACT(MINUTE FROM '2026-07-15 14:30:45') AS minute_part;

Output

OUTPUT

+-----------+------------+------------------+-------------+
| year_part | month_part | year_month_value | minute_part |
+-----------+------------+------------------+-------------+
|      2026 |          7 |           202607 |          30 |
+-----------+------------+------------------+-------------+
1 row in set

Adding and subtracting

query.sql

SQL

SELECT DATE_ADD('2026-07-15', INTERVAL 10 DAY)   AS plus_10_days,
       DATE_SUB('2026-07-15', INTERVAL 1 MONTH)  AS minus_1_month,
       '2026-07-15' + INTERVAL 1 YEAR            AS plus_1_year,
       '2026-07-15 10:00:00' + INTERVAL 90 MINUTE AS plus_90_minutes,
       DATE_ADD('2026-01-31', INTERVAL 1 MONTH)  AS month_end_rolls_back;

Output

OUTPUT

+--------------+---------------+-------------+---------------------+----------------------+
| plus_10_days | minus_1_month | plus_1_year | plus_90_minutes     | month_end_rolls_back |
+--------------+---------------+-------------+---------------------+----------------------+
| 2026-07-25   | 2026-06-15    | 2027-07-15  | 2026-07-15 11:30:00 | 2026-02-28           |
+--------------+---------------+-------------+---------------------+----------------------+
1 row in set

Notice the last column: adding a month to January 31 gives February 28, the last valid day, not an error and not March 3. The units are MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER and YEAR.

Differences

query.sql

SQL

SELECT DATEDIFF('2026-12-25', '2026-07-15')                     AS days_between,
       TIMESTAMPDIFF(MONTH, '2026-01-15', '2026-07-14')         AS full_months,
       TIMESTAMPDIFF(YEAR, '1990-08-15', '2026-07-15')          AS age_years,
       TIMESTAMPDIFF(HOUR, '2026-07-15 08:00:00', '2026-07-15 17:30:00') AS hours,
       TIMEDIFF('17:30:00', '08:00:00')                          AS time_difference;

Output

OUTPUT

+--------------+-------------+-----------+-------+-----------------+
| days_between | full_months | age_years | hours | time_difference |
+--------------+-------------+-----------+-------+-----------------+
|          163 |           5 |        35 |     9 | 09:30:00        |
+--------------+-------------+-----------+-------+-----------------+
1 row in set

DATEDIFF(later, earlier) gives whole days, and only looks at the date part.
TIMESTAMPDIFF(unit, earlier, later) counts complete units. The order of arguments is the reverse of DATEDIFF, which trips up many people.
Between 2026-01-15 and 2026-07-14, there are 5 complete months, not 6.

First and last day of the month

query.sql

SQL

SELECT LAST_DAY('2026-02-10')                              AS last_of_february,
       DATE_FORMAT('2026-07-15', '%Y-%m-01')               AS first_of_month,
       LAST_DAY('2026-07-15') + INTERVAL 1 DAY              AS first_of_next_month,
       DAY(LAST_DAY('2024-02-01'))                          AS days_in_feb_2024;

Output

OUTPUT

+------------------+----------------+---------------------+------------------+
| last_of_february | first_of_month | first_of_next_month | days_in_feb_2024 |
+------------------+----------------+---------------------+------------------+
| 2026-02-28       | 2026-07-01     | 2026-08-01          |               29 |
+------------------+----------------+---------------------+------------------+
1 row in set

Date from parts, and from numbers

query.sql

SQL

SELECT MAKEDATE(2026, 100)          AS day_100_of_2026,
       STR_TO_DATE('15/07/2026', '%d/%m/%Y') AS parsed_date,
       FROM_UNIXTIME(0)              AS unix_epoch_in_session_zone,
       UNIX_TIMESTAMP('1970-01-02 00:00:00') AS seconds_for_one_day_after_epoch,
       TO_DAYS('2026-07-15') - TO_DAYS('2026-01-01') AS day_count;

Output

OUTPUT

+-----------------+-------------+----------------------------+---------------------------------+-----------+
| day_100_of_2026 | parsed_date | unix_epoch_in_session_zone | seconds_for_one_day_after_epoch | day_count |
+-----------------+-------------+----------------------------+---------------------------------+-----------+
| 2026-04-10      | 2026-07-15  | 1970-01-01 00:00:00        |                           86400 |       195 |
+-----------------+-------------+----------------------------+---------------------------------+-----------+

(The two Unix timestamp columns depend on the session time zone, so the output of this example is not compared. In a session set to UTC, the values are those shown.)

Truncating to a day

query.sql

SQL

1
2
3

SELECT DATE('2026-07-15 14:30:45') AS date_only,
       TIME('2026-07-15 14:30:45') AS time_only,
       DATE_FORMAT('2026-07-15 14:30:45', '%Y-%m-%d %H:00:00') AS rounded_down_to_hour;

Output

OUTPUT

+------------+-----------+----------------------+
| date_only  | time_only | rounded_down_to_hour |
+------------+-----------+----------------------+
| 2026-07-15 | 14:30:45  | 2026-07-15 14:00:00  |
+------------+-----------+----------------------+
1 row in set

Remember

Do not apply these functions to columns in a WHERE clause if you want to use an index (Filtering by Date).
A date with a month or day of zero, or '2026-02-30', is rejected in strict mode.
For TIMESTAMP columns, session time zone changes what you see.
Formatting for display is done with DATE_FORMAT, covered in the next lesson.

lightbulb

Use the standard form

INTERVAL arithmetic (date + INTERVAL 7 DAY) is easy to read and works the same in other databases that support intervals.