MySQL Date & Time Functions

Date and time functions are built-in MySQL functions used to retrieve, format, extract, and calculate date and time values.

SELECT NOW();

This returns the current date and time.

The NOW() function returns the current date and time.

SELECT NOW();

A typical result looks like:

2026-09-21 13:30:25

The actual result depends on the MySQL server's current date and time.

CURRENT_TIMESTAMP() returns the current date and time.

SELECT CURRENT_TIMESTAMP();

It is commonly used when inserting or updating records that need the current timestamp.

INSERT INTO payments
(student_id, amount, payment_date)
VALUES
(101, 5000, CURRENT_TIMESTAMP());

The CURDATE() function returns the current date without the current time.

SELECT CURDATE();

Typical result:

2026-09-21

CURRENT_DATE() returns the current date.

SELECT CURRENT_DATE();

It is equivalent to using CURDATE().

SELECT CURDATE();
SELECT CURRENT_DATE();

The CURTIME() function returns the current time.

SELECT CURTIME();

A typical result may look like:

13:30:25

The exact value changes according to the current time.

CURRENT_TIME() returns the current time.

SELECT CURRENT_TIME();

It is equivalent to CURTIME().

The DATE() function extracts the date portion from a date-time value.

SELECT DATE('2026-09-21 13:30:25');

Result:

2026-09-21

Example:

SELECT DATE(payment_date)
FROM payments;

The TIME() function extracts the time portion from a date-time value.

SELECT TIME('2026-09-21 13:30:25');

Result:

13:30:25

The YEAR() function extracts the year from a date.

SELECT YEAR('2026-09-21');

Result:

2026

Example:

SELECT YEAR(admission_date)
FROM students;

The MONTH() function returns the month number from a date.

SELECT MONTH('2026-09-21');

Result:

9

January is 1 and December is 12.

The MONTHNAME() function returns the name of the month.

SELECT MONTHNAME('2026-09-21');

Result:

September

The DAY() function returns the day of the month.

SELECT DAY('2026-09-21');

Result:

21

The DAYNAME() function returns the name of the weekday.

SELECT DAYNAME('2026-09-21');

The result is the weekday name corresponding to the supplied date.

The DAYOFWEEK() function returns a number representing the weekday.

SELECT DAYOFWEEK('2026-09-21');

MySQL uses:

• 1 = Sunday
• 2 = Monday
• 3 = Tuesday
• 4 = Wednesday
• 5 = Thursday
• 6 = Friday
• 7 = Saturday

The DAYOFYEAR() function returns the day number within the year.

SELECT DAYOFYEAR('2026-01-01');

Result:

1

For a date later in the year, the returned number is correspondingly larger.

The WEEK() function returns the week number for a date.

SELECT WEEK('2026-09-21');

The exact week number depends on the MySQL week mode being used.

The HOUR() function extracts the hour from a time or date-time value.

SELECT HOUR('13:45:30');

Result:

13

The MINUTE() function extracts the minute from a time value.

SELECT MINUTE('13:45:30');

Result:

45

The SECOND() function extracts the seconds from a time value.

SELECT SECOND('13:45:30');

Result:

30

The DATE_ADD() function adds a specified time interval to a date.

SELECT DATE_ADD('2026-09-21', INTERVAL 10 DAY);

Result:

2026-10-01

Example:

SELECT DATE_ADD(admission_date, INTERVAL 6 MONTH)
FROM students;

The DATE_SUB() function subtracts a specified interval from a date.

SELECT DATE_SUB('2026-09-21', INTERVAL 10 DAY);

Result:

2026-09-11

The DATEDIFF() function returns the number of days between two dates.

SELECT DATEDIFF('2026-09-21', '2026-09-01');

Result:

20

DATEDIFF() considers the date portions and returns the difference in days.

TIMESTAMPDIFF() calculates the difference between two date-time values using a specified unit.

SELECT TIMESTAMPDIFF(
    MONTH,
    '2026-01-01',
    '2026-09-01'
);

Result:

8

It can calculate differences in units such as YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.

The DATE_FORMAT() function formats a date according to a specified format.

SELECT DATE_FORMAT(
    '2026-09-21',
    '%d-%m-%Y'
);

Result:

21-09-2026

Some common format codes are:

• %d = Day
• %m = Month number
• %Y = Four-digit year
• %M = Month name
• %W = Weekday name

The STR_TO_DATE() function converts a string into a date or date-time value using a specified format.

SELECT STR_TO_DATE(
    '21-09-2026',
    '%d-%m-%Y'
);

Result:

2026-09-21

This is useful when date values arrive as formatted text.

The LAST_DAY() function returns the last date of the month for a given date.

SELECT LAST_DAY('2026-09-21');

Result:

2026-09-30

This is useful for monthly reports and billing calculations.

Date functions can be used with WHERE to filter records by date information.

SELECT *
FROM students
WHERE YEAR(admission_date) = 2026;

This returns students whose admission date is in the year 2026.

Another example:

SELECT *
FROM payments
WHERE MONTH(payment_date) = 9;

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    admission_date DATE,
    created_at DATETIME
);

INSERT INTO students
(name, admission_date, created_at)
VALUES
('Amit Kumar', '2026-01-15', '2026-01-15 10:30:00'),
('Priya Singh', '2026-04-20', '2026-04-20 11:15:00'),
('Rahul Sharma', '2026-09-10', '2026-09-10 09:45:00');

-- Display current date
SELECT CURDATE();

-- Display current date and time
SELECT NOW();

-- Extract admission year
SELECT
    name,
    YEAR(admission_date) AS admission_year
FROM students;

-- Extract admission month
SELECT
    name,
    MONTH(admission_date) AS admission_month
FROM students;

-- Add six months to admission date
SELECT
    name,
    admission_date,
    DATE_ADD(
        admission_date,
        INTERVAL 6 MONTH
    ) AS course_end_date
FROM students;

-- Find days since admission
SELECT
    name,
    DATEDIFF(CURDATE(), admission_date)
    AS days_since_admission
FROM students;

-- Format admission date
SELECT
    name,
    DATE_FORMAT(
        admission_date,
        '%d-%m-%Y'
    ) AS formatted_date
FROM students;

This example demonstrates how date and time functions can be used in a student management system.