MySQL Date and Time Functions (original) (raw)

Last Updated : 2 May, 2026

MySQL provides built-in date and time functions that allow users to work efficiently with date-related data in databases. These functions help in managing timestamps, scheduling events, and performing calculations on dates, which are essential for applications that rely on time-based information. They are used to:

Datatypes

MySQL provides the following data types for storing date and time values:

data_types

Functions

Now, let's dive into the MySQL date functions that you can use to manipulate and query date and time data effectively.

1. NOW()

The NOW() function retrieves the current date and time in YYYY-MM-DD HH:MI:SS format.

**Query:

SELECT NOW();

**Output:

Screenshot-2026-02-02-154532

2. CURDATE()

If we only need the current date without the time portion, we can use CURDATE(), which returns the date in YYYY-MM-DD format.

**Query:

SELECT CURDATE();

**Output:

Screenshot-2026-02-02-154857

3. CURTIME()

The CURTIME() function returns the current time in HH:MI:SS format, excluding the date.

**Query:

SELECT CURTIME();

**Output:

Screenshot-2026-02-02-155034

The DATE() function extracts only the date part from a DATETIME or TIMESTAMP value, discarding the time.

**Example: Create a table name Test :

Screenshot-2026-02-02-155310

**Query:

SELECT Name, DATE(BirthTime)
AS BirthDate FROM Test;

**Output:

Screenshot-2026-02-02-155447

The EXTRACT() function is used to retrieve a specific part of a date or datetime value, making date-based analysis and comparisons easier.

**Syntax:

EXTRACT(unit FROM date);

**Query: To Extract Day

SELECT Name, Extract(DAY FROM
BirthTime) AS BirthDay FROM Test;

**Output:

Screenshot-2026-02-02-155729

**Query: To Extract Year

SELECT Name, Extract(YEAR FROM BirthTime)
AS BirthYear FROM Test;

**Output:

Screenshot-2026-02-02-160027

**Query: To Extract Seconds

SELECT Name, Extract(SECOND FROM
BirthTime) AS BirthSecond FROM Test;

**Output:

Screenshot-2026-02-02-160301

**6. DATE_ADD()

The DATE_ADD() function allows you to add time intervals (e.g., days, months, years) to a date or DATETIME value.

**Syntax:

DATE_ADD(date, INTERVAL expr type);

**Example: For the below table named '**Test'

Screenshot-2026-02-02-160847

**Query: To Add 1 Year to a Date

SELECT Name, DATE_ADD(BirthTime, INTERVAL
1 YEAR) AS BirthTimeModified FROM Test;

**Output:

Screenshot-2026-02-02-161053

**Query: To Add 30 days to a Date

SELECT Name, DATE_ADD(BirthTime,
INTERVAL 30 DAY) AS BirthDayModified FROM Test;

**Output:

Screenshot-2026-02-02-161314

**Query: To Add 4 Hours to a Date

SELECT Name, DATE_ADD(BirthTime, INTERVAL
4 HOUR) AS BirthHourModified FROM Test;

**Output:

Screenshot-2026-02-02-161637

**7. DATEDIFF()

This Function returns the number of days between two dates.

**Syntax:

DATEDIFF(date1, date2);

**Query: To Find the Difference Between Two Dates

SELECT DATEDIFF('2017-01-13', '2017-01-03') AS DateDiff;

**Output:

Screenshot-2026-02-02-161910

**8. DATE_FORMAT()

DATE_FORMAT() allows us to format a DATE, DATETIME, or TIMESTAMP value into a custom format using placeholders.

**Syntax:

DATE_FORMAT(date,format);

the date is a valid date and the format specifies the output format for the date/time. The formats that can be used are:

**Query: To Format a Date

SELECT DATE_FORMAT('2025-04-10 12:34:56', '%W, %M %d, %Y') AS formatted_date;

**Output:

Screenshot-2026-02-02-162236

Date & Time Best Practices in MySQL

Follow best practices when working with date and time in MySQL to ensure accurate data storage, efficient querying, and consistent handling across different time zones and applications.

SELECT * FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-01-31';