SQL Date Functions

Date functions are SQL functions used to work with date and time values.

SELECT CURDATE();

This returns the current date.

CURDATE() returns the current date.

SELECT CURDATE();

Example output:

2026-09-20

CURRENT_DATE returns the current date.

SELECT CURRENT_DATE;

It is similar to CURDATE().

CURTIME() returns the current time.

SELECT CURTIME();

Example output:

16:30:25

CURRENT_TIME returns the current time.

SELECT CURRENT_TIME;

It is similar to CURTIME().

NOW() returns the current date and time.

SELECT NOW();

Example output:

2026-09-20 16:30:25

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

SELECT DATE('2026-09-20 16:30:25');

Output:

2026-09-20

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

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

Output:

2026

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

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

Output:

9

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

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

Output:

20

DAYNAME() returns the name of the weekday.

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

Example output:

Sunday

MONTHNAME() returns the name of the month.

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

Output:

September

DAYOFWEEK() returns a number representing the weekday.

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

In MySQL, Sunday is represented by 1, Monday by 2, and so on.

DAYOFMONTH() returns the day of the month.

SELECT DAYOFMONTH('2026-09-20');

Output:

20

DAYOFYEAR() returns the day number within the year.

SELECT DAYOFYEAR('2026-09-20');

It returns a number between 1 and 365, or 366 in a leap year.

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

SELECT HOUR('2026-09-20 16:30:25');

Output:

16

The MINUTE() function extracts the minute.

SELECT MINUTE('2026-09-20 16:30:25');

Output:

30

The SECOND() function extracts the seconds.

SELECT SECOND('2026-09-20 16:30:25');

Output:

25

DATE_FORMAT() is used to display a date in a specific format.

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

Output:

20-09-2026

Common format codes include %d for day, %m for month, and %Y for four-digit year.

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

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

Output:

2026-09-30

DATE_SUB() subtracts a specified time interval from a date.

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

Output:

2026-09-10

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

SELECT DATEDIFF('2026-09-30', '2026-09-20');

Output:

10

TIMESTAMPDIFF() calculates the difference between two dates or datetime values using a specified unit.

SELECT TIMESTAMPDIFF(YEAR, '2020-09-20', '2026-09-20');

Output:

6

You can use units such as YEAR, MONTH, and DAY.

LAST_DAY() returns the last date of the month.

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

Output:

2026-09-30

EXTRACT() is used to extract a specific part of a date or datetime value.

SELECT EXTRACT(YEAR FROM '2026-09-20');

Output:

2026

You can extract values such as YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.

STR_TO_DATE() converts a string into a date using a specified format.

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

Output:

2026-09-20

Dates can be compared using operators such as >, <, =, >=, and <=.

SELECT *
FROM students
WHERE admission_date > '2026-01-01';

This returns students admitted after January 1, 2026.

Date columns can contain NULL. Use IS NULL or IS NOT NULL to check them.

SELECT *
FROM students
WHERE joining_date IS NULL;

This finds students whose joining date has not been entered.

Suppose we have a students table containing an admission_date column.

SELECT
    student_name,
    admission_date,
    YEAR(admission_date) AS admission_year,
    MONTHNAME(admission_date) AS admission_month
FROM students;

This displays the student's name, admission date, admission year, and admission month.

We can combine several date functions in one query.

SELECT
    student_name,
    admission_date,
    YEAR(admission_date) AS year,
    MONTHNAME(admission_date) AS month,
    DAY(admission_date) AS day,
    DATE_FORMAT(admission_date, '%d-%m-%Y') AS formatted_date
FROM students;

This query displays useful information from the student's admission date.