SQL Date and Time Functions

Lesson 3.4 · Reading time: ~8 min · Practice tasks ↓

SQL date and time functions return the current date, extract parts of a date, add and subtract intervals, calculate the difference between dates and format dates for output. In this lesson you'll learn the most used functions with examples on the rental data of the Sakila database, and see how their names differ between databases.

Almost every business table has dates: when an order was placed, when a film was rented, when a payment was made. Questions like "how long was the film rented?", "when is it due back?" or "how many rentals were in July?" are answered with date functions.

The examples use MySQL and MariaDB syntax. Date functions differ between databases more than any other group, so a comparison table is given at the end of the lesson.

The Current Date and Time

SELECT CURRENT_DATE AS today,
       CURRENT_TIME AS now_time,
       NOW() AS now_datetime;

Result: one row with the current date, time, and date with time on the database server.

CURRENT_DATE, CURRENT_TIME and CURRENT_TIMESTAMP are written without parentheses; they are SQL standard expressions. The value is fixed at the start of the statement, so all rows of one query get the same time.

Extracting Parts of a Date

DATE() and TIME() split a datetime value into its date and time; YEAR(), MONTH() and DAY() return single numbers:

SELECT rental_date,
       DATE(rental_date) AS rental_day,
       TIME(rental_date) AS rental_time,
       YEAR(rental_date) AS rental_year,
       MONTH(rental_date) AS rental_month,
       DAY(rental_date) AS day_of_month
FROM rental
LIMIT 3;

Result: for each of the first three rentals, the full datetime split into the date, the time, the year, the month and the day.

The standard function EXTRACT() does the same and works in most databases:

SELECT rental_date,
       EXTRACT(YEAR FROM rental_date) AS rental_year,
       EXTRACT(MONTH FROM rental_date) AS rental_month
FROM rental
LIMIT 3;

Extracted parts are useful in filters:

SELECT rental_id, rental_date
FROM rental
WHERE YEAR(rental_date) = 2005
  AND MONTH(rental_date) = 7
LIMIT 5;

Result: five rentals made in July 2005.

A function on a column prevents the database from using an index on it. On large tables, a date range is faster: WHERE rental_date >= '2005-07-01' AND rental_date < '2005-08-01'. You'll learn why in the lessons about indexes.

Adding and Subtracting Intervals

DATE_ADD() and DATE_SUB() shift a date by an interval. The unit can be SECOND, MINUTE, HOUR, DAY, WEEK, MONTH or YEAR.

SELECT rental_id,
       rental_date,
       DATE_ADD(rental_date, INTERVAL 7 DAY) AS due_date,
       DATE_SUB(rental_date, INTERVAL 1 MONTH) AS month_before
FROM rental
LIMIT 3;

Result: each rental's date, the date seven days later, and the date one month earlier.

MySQL and MariaDB also accept the short form rental_date + INTERVAL 7 DAY.

The Difference Between Two Dates

DATEDIFF(end, start) returns the number of days between two dates and ignores the time:

SELECT rental_id,
       rental_date,
       return_date,
       DATEDIFF(return_date, rental_date) AS days_rented
FROM rental
WHERE return_date IS NOT NULL
LIMIT 5;

Result: how many calendar days each of five returned rentals lasted.

TIMESTAMPDIFF(unit, start, end) counts full units of any size; note that the start date comes first:

SELECT rental_id,
       TIMESTAMPDIFF(HOUR, rental_date, return_date) AS hours_rented,
       TIMESTAMPDIFF(DAY, rental_date, return_date) AS full_days_rented
FROM rental
WHERE return_date IS NOT NULL
LIMIT 5;

Result: the rental duration in hours and in full 24-hour days. full_days_rented can be smaller than DATEDIFF, which counts calendar days.

Formatting Dates

DATE_FORMAT(date, format) turns a date into text in the given format:

SELECT rental_date,
       DATE_FORMAT(rental_date, '%d.%m.%Y') AS european_date,
       DATE_FORMAT(rental_date, '%M %e, %Y %H:%i') AS long_date
FROM rental
LIMIT 3;

Result: the date as 24.05.2005 and as May 24, 2005 22:53.

The result of DATE_FORMAT() is text, not a date. Format dates only for output; compare and sort the original date values.

Date Functions in Other Databases

EXTRACT(part FROM date) and CURRENT_DATE / CURRENT_TIMESTAMP are standard and work almost everywhere.

Key Takeaways:

• CURRENT_DATE, CURRENT_TIMESTAMP and NOW() return the current date and time.
• DATE(), YEAR(), MONTH(), DAY() and EXTRACT() take parts of a date.
• DATE_ADD() and DATE_SUB() shift a date by an interval.
• DATEDIFF() counts calendar days; TIMESTAMPDIFF() counts full units of any size.
• DATE_FORMAT() returns text: use it for output, not for comparisons.
• Date function names differ a lot between databases; check the table above when you switch.

Frequently Asked Questions

How do I get the current date in SQL?

Use CURRENT_DATE for the date only, and CURRENT_TIMESTAMP or NOW() for the date with time. In SQL Server, use GETDATE() or CAST(GETDATE() AS DATE).

How do I calculate the number of days between two dates in SQL?

In MySQL and MariaDB, use DATEDIFF(end_date, start_date). In SQL Server, DATEDIFF(day, start_date, end_date). In PostgreSQL, subtract one date from another: end_date::date - start_date::date.

How do I add days to a date in SQL?

In MySQL and MariaDB, use DATE_ADD(date, INTERVAL 7 DAY) or date + INTERVAL 7 DAY. In PostgreSQL, date + INTERVAL '7 days'; in SQL Server, DATEADD(day, 7, date).