SQL Date & Time Functions: NOW, DATEDIFF, DATE_FORMAT, and More

Dates and times are everywhere in real data — order timestamps, birthdates, subscription renewal dates, log entries — and rarely come back out of a database in exactly the display format or calculated form an application needs. MySQL's date and time functions handle formatting, arithmetic, and extraction directly inside SQL, avoiding the need for separate date-handling logic in application code for most common cases.

What Are Date & Time Functions?

Date and time functions are built-in MySQL functions for retrieving the current date/time, formatting dates for display, calculating the difference between two dates, adding or subtracting time intervals, and extracting individual components (year, month, day, weekday) from a date or datetime value.

What You'll Learn

• Retrieve the current date and time using NOW, CURDATE, and CURTIME.
• Format dates for display using DATE_FORMAT.
• Calculate differences between dates using DATEDIFF and TIMESTAMPDIFF.
• Add and subtract time intervals using DATE_ADD and DATE_SUB, and extract date parts.

Key Terms to Know

• NOW(): Returns the current date and time as a DATETIME value.
• DATE_FORMAT: Formats a date/time value into a specified display string pattern.
• DATEDIFF: Returns the number of days between two dates.
• DATE_ADD / DATE_SUB: Add or subtract a specified time interval from a date.

Getting the Current Date and Time: NOW, CURDATE, CURTIME

NOW() returns the current date and time together as a DATETIME value, such as '2026-06-24 14:32:10', commonly used for timestamp columns like created_at when a row is inserted. CURDATE() returns just today's date with no time component, useful when only the calendar date matters, such as comparing against a DATE column. CURTIME() returns just the current time with no date component, useful for time-of-day-only comparisons.

These are frequently used as DEFAULT values in CREATE TABLE statements (as covered back in Module 2), automatically timestamping new rows without requiring application code to supply the value explicitly.

Formatting Dates for Display with DATE_FORMAT

DATE_FORMAT(order_date, '%d %b %Y') converts a raw DATE or DATETIME value into a custom display string, such as '24 Jun 2026', using format specifiers like %d (day), %b (abbreviated month name), %Y (4-digit year), %H (24-hour), and %i (minutes). This is the standard way to present dates in a human-friendly format directly from a query, rather than returning raw DATETIME values and reformatting them entirely in application code.

Common format combinations include '%Y-%m-%d' for an ISO-style date, '%d/%m/%Y' for a common international format, and '%h:%i %p' for a 12-hour time display with AM/PM.

Date Arithmetic: DATEDIFF, TIMESTAMPDIFF, DATE_ADD, DATE_SUB

DATEDIFF(end_date, start_date) returns the number of whole days between two dates, such as DATEDIFF(delivery_date, order_date) to calculate shipping duration in days. TIMESTAMPDIFF(unit, start, end) is more flexible, returning the difference in a specified unit like YEAR, MONTH, DAY, or HOUR, such as TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) to calculate someone's current age in completed years.

DATE_ADD(order_date, INTERVAL 7 DAY) adds a specified interval to a date, useful for calculating an expected delivery date or subscription renewal date. DATE_SUB works identically but subtracts the interval instead, such as DATE_SUB(NOW(), INTERVAL 30 DAY) to find the cutoff date for 'orders placed in the last 30 days.'

YEAR(), MONTH(), DAY(), DAYNAME(), and WEEKDAY() each extract a specific component from a date — YEAR(order_date) returns just the year as an integer, DAYNAME(order_date) returns the full weekday name like 'Wednesday', and WEEKDAY(order_date) returns a numeric weekday index (0 for Monday through 6 for Sunday).

Visual Summary

Picture a raw timestamp as a single long, precise scientific measurement, like '2026-06-24 14:32:10.483291'. Date and time functions are a set of specialized lenses you can hold up to that measurement: DATE_FORMAT shows it dressed up nicely for a human to read, DATEDIFF measures the gap between two such measurements in whole days, DATE_ADD projects the measurement forward in time by a chosen interval, and YEAR/MONTH/DAYNAME each zoom in on just one specific component of it.

Core Date & Time Functions

Function	Purpose	Example Result
NOW()	Current date and time	'2026-06-24 14:32:10'
CURDATE()	Current date only	'2026-06-24'
DATE_FORMAT(d, fmt)	Custom display formatting	'24 Jun 2026'
DATEDIFF(end, start)	Days between two dates	DATEDIFF('2026-07-01','2026-06-24') → 7
TIMESTAMPDIFF(unit, s, e)	Difference in a chosen unit	TIMESTAMPDIFF(YEAR, dob, CURDATE())
DATE_ADD(d, INTERVAL n unit)	Adds a time interval	DATE_ADD(CURDATE(), INTERVAL 7 DAY)

SQL Example

-- Format an order date for friendly display
SELECT order_id, DATE_FORMAT(order_date, '%d %b %Y') AS formatted_date
FROM orders;

-- Calculate shipping duration in days
SELECT order_id, DATEDIFF(delivery_date, order_date) AS shipping_days
FROM orders;

-- Calculate customer age in completed years
SELECT customer_id, TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) AS age
FROM customers;

-- Find orders placed in the last 30 days
SELECT order_id, order_date
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

-- Extract weekday name to analyze order patterns by day
SELECT order_id, DAYNAME(order_date) AS order_weekday
FROM orders;

Each query demonstrates a distinct real-world use: friendly date formatting for display, calculating a duration between two dates, computing age from a birthdate, finding rows within a relative recent window using DATE_SUB, and extracting just the weekday name for pattern analysis — all performed directly within SQL without any date-handling logic needed in application code.

Real-World Examples

• E-commerce dashboards use DATEDIFF to report average shipping duration between order and delivery dates.
• Subscription platforms use DATE_ADD to automatically calculate the next renewal or trial expiration date.
• HR systems use TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) to calculate years of tenure for service-award eligibility.
• Retail analytics use DAYNAME or WEEKDAY to discover which days of the week see the highest order volume.

Best Practices and Pro Tips

• Use TIMESTAMPDIFF instead of DATEDIFF when you need a difference measured in a unit other than days, such as months or years, since DATEDIFF is fixed to whole days only.
• Always perform date filtering using actual date comparisons (like DATE_SUB) rather than string manipulation, which only works reliably when the column is properly typed as DATE/DATETIME, reinforcing the data-typing lesson from Module 2.
• Use DATE_FORMAT specifically for final display output, but keep underlying calculations (DATEDIFF, comparisons, sorting) on the raw, unformatted DATE/DATETIME value for correctness.

Common Mistakes to Avoid

• Confusing DATEDIFF's argument order — it's DATEDIFF(end_date, start_date), and reversing the arguments produces a negative result instead of the expected positive duration.
• Using DATEDIFF when a non-day unit (like months or years) was actually needed, instead of reaching for TIMESTAMPDIFF.
• Formatting a date with DATE_FORMAT before performing calculations or comparisons on it, which converts it to a string and breaks proper date-based logic.
• Forgetting that NOW() includes time, so comparisons against it (like WHERE event_date = NOW()) rarely match exactly unless time components are explicitly accounted for.

Interview Questions

Q1. What is the difference between NOW() and CURDATE()?

NOW() returns the current date and time together as a DATETIME value, while CURDATE() returns only today's date with no time component, as a DATE value.

Q2. How would you calculate someone's age in completed years from their date of birth?

Using TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()), which correctly calculates the number of completed years between the birthdate and today's date, accounting for whether the birthday has occurred yet this year.

Q3. What is the correct argument order for DATEDIFF, and what does it return?

DATEDIFF(end_date, start_date) returns the number of days between the two dates as end_date minus start_date; reversing the argument order produces a negative result instead of the expected positive duration.

Q4. How would you find all rows with a date column within the last 30 days?

Using WHERE date_column >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);, which calculates the cutoff date 30 days before today and filters for rows on or after that date.

Practice MCQs

1. Which function returns the current date and time together?

1. CURDATE()
2. CURTIME()
3. NOW()
4. DATEDIFF()

Answer: C. NOW()

Explanation: NOW() returns both the current date and time as a single DATETIME value, unlike CURDATE() and CURTIME() which return only one component each.

2. What does DATEDIFF('2026-07-01', '2026-06-24') return?

1. -7
2. 7
3. 0
4. An error

Answer: B. 7

Explanation: DATEDIFF(end_date, start_date) returns 7, since there are 7 days between June 24 and July 1, 2026.

3. Which function would correctly calculate a customer's age in years?

1. DATEDIFF(YEAR, dob, CURDATE())
2. TIMESTAMPDIFF(YEAR, dob, CURDATE())
3. DATE_ADD(dob, INTERVAL 1 YEAR)
4. YEAR(dob) - YEAR(CURDATE())

Answer: B. TIMESTAMPDIFF(YEAR, dob, CURDATE())

Explanation: TIMESTAMPDIFF supports specifying YEAR as the unit and correctly accounts for whether the birthday has occurred yet this year, unlike a simple year subtraction.

Quick Revision Points

• NOW() returns date+time; CURDATE() returns date only; CURTIME() returns time only.
• DATEDIFF(end, start) returns whole days; TIMESTAMPDIFF(unit, start, end) supports other units.
• DATE_ADD/DATE_SUB add or subtract a specified INTERVAL from a date.
• YEAR, MONTH, DAY, DAYNAME, and WEEKDAY extract individual date components.

Conclusion

• Date and time functions let MySQL handle formatting, arithmetic, and extraction natively, without external date-handling code.
• TIMESTAMPDIFF's flexible unit support makes it more broadly useful than the days-only DATEDIFF.
• Performing calculations on raw DATE/DATETIME values, and formatting only for final display, avoids a whole category of subtle bugs.

MySQL's date and time functions cover retrieving the current date/time (NOW, CURDATE, CURTIME), formatting for display (DATE_FORMAT), calculating differences (DATEDIFF, TIMESTAMPDIFF), shifting dates by an interval (DATE_ADD, DATE_SUB), and extracting individual components (YEAR, MONTH, DAY, DAYNAME, WEEKDAY). Together they eliminate the need for most date-handling logic in application code. The next lesson covers aggregate functions — COUNT, SUM, AVG, MIN, and MAX — for summarizing data across many rows at once.