MySQL Date Functions: NOW, CURDATE, DATE_FORMAT, and DATEDIFF

1. NOW(): Getting the Current Date and Time

The NOW() function returns the current date and time in the format YYYY-MM-DD HH:MM:SS. It is particularly useful for timestamps and logging events.

Syntax

NOW()

Example

SELECT NOW() AS current_datetime;

Explanation:

1. NOW() fetches the current system date and time.
2. The output format is YYYY-MM-DD HH:MM:SS.
3. Example output: 2026-04-27 14:35:42.

Practical Use Case

INSERT INTO orders (order_id, customer_id, order_date)
VALUES (101, 1, NOW());

• Automatically stores the current timestamp when a new order is placed.
• Ensures accurate tracking of transactions.

2. CURDATE(): Retrieving the Current Date

While NOW() returns both date and time, CURDATE() returns only the current date in YYYY-MM-DD format. This function is ideal for daily reports or date comparisons.

Syntax

CURDATE()

Example

SELECT CURDATE() AS today_date;

Explanation:

• Returns the current date without the time component.
• Example output: 2026-04-27.

Practical Example

SELECT *
FROM events
WHERE event_date = CURDATE();

• Retrieves all events scheduled for today.
• Useful for dashboards or daily notifications.

3. DATE_FORMAT(): Customizing Date Output

DATE_FORMAT() allows you to display dates in a variety of formats according to your needs. It is highly flexible and widely used for reporting.

Syntax

DATE_FORMAT(date, format)

• date: The date value to format.
• format: The desired output format using specifiers like %Y, %m, %d.

Example

SELECT DATE_FORMAT(NOW(), '%W, %M %d, %Y %h:%i %p') AS formatted_date;

Explanation:

1. %W → Full weekday name (e.g., Monday)
2. %M → Full month name (e.g., April)
3. %d → Day of the month with leading zeros
4. %Y → 4-digit year
5. %h:%i %p → 12-hour time with minutes and AM/PM

Output Example: Sunday, April 27, 2026 02:35 PM

Real-World Use Case

SELECT DATE_FORMAT(order_date, '%d-%m-%Y') AS formatted_order_date
FROM orders;

• Converts the order_date into DD-MM-YYYY format for invoices or reports.

4. DATEDIFF(): Calculating Date Differences

The DATEDIFF() function calculates the difference in days between two dates. It is essential for age calculations, subscription tracking, and deadline management.

Syntax

DATEDIFF(date1, date2)

• Returns the number of days between date1 and date2.
• If date1 is later than date2, the result is positive; otherwise, it’s negative.

Example

SELECT DATEDIFF('2026-05-10', '2026-04-27') AS days_difference;

Explanation:

1. date1 = 2026-05-10
2. date2 = 2026-04-27
3. Difference = 13 days

Practical Use Case

SELECT customer_id, DATEDIFF(CURDATE(), last_login) AS days_inactive
FROM users
WHERE DATEDIFF(CURDATE(), last_login) > 30;

• Identifies users who have been inactive for more than 30 days.
• Useful for retention strategies and automated reminders.

Combining Date Functions for Advanced Queries

You can combine these functions to create more sophisticated queries. For example, generating a report of users who registered in the last 7 days with a nicely formatted date:

SELECT user_id,
       DATE_FORMAT(registration_date, '%d %b %Y') AS formatted_registration,
       DATEDIFF(CURDATE(), registration_date) AS days_since_registration
FROM users
WHERE DATEDIFF(CURDATE(), registration_date) <= 7;

Explanation:

1. DATE_FORMAT makes the registration date readable.
2. DATEDIFF calculates how many days ago the registration occurred.
3. WHERE filters users who joined within the last 7 days.