SQL Date and Time Functions

SQL provides a rich set of date and time functions for working with date and time values stored in a database. These functions can retrieve the current date and time, extract specific parts of a date (like year, month, or day), calculate the difference between two dates, and format dates for display.

Date and time handling is essential in almost every real-world application — for recording when orders were placed, tracking student enrollment dates, calculating ages, or generating monthly reports.

The Reference Table

1. Getting the Current Date and Time

SELECT NOW()     AS CurrentDateTime,
       CURDATE() AS TodayDate,
       CURTIME() AS CurrentTime;

2. Extracting Parts of a Date

YEAR(), MONTH(), DAY()

SELECT StudentName, BirthDate,
       YEAR(BirthDate)  AS BirthYear,
       MONTH(BirthDate) AS BirthMonth,
       DAY(BirthDate)   AS BirthDay
FROM Students;

Result:

HOUR(), MINUTE(), SECOND()

SELECT HOUR('14:30:45')   AS HourPart,
       MINUTE('14:30:45') AS MinutePart,
       SECOND('14:30:45') AS SecondPart;

Result: 14 | 30 | 45

EXTRACT() — Universal Part Extraction

EXTRACT(part FROM date) is a more readable alternative that works consistently across different database systems.

SELECT StudentName,
       EXTRACT(YEAR  FROM BirthDate) AS BirthYear,
       EXTRACT(MONTH FROM BirthDate) AS BirthMonth
FROM Students;

DAYNAME() and MONTHNAME()

SELECT JoinDate,
       DAYNAME(JoinDate)   AS JoinDayName,
       MONTHNAME(JoinDate) AS JoinMonthName
FROM Students;

Result for Ravi (JoinDate: 2024-01-10):

DAYOFWEEK(), DAYOFMONTH(), DAYOFYEAR()

SELECT JoinDate,
       DAYOFWEEK(JoinDate)  AS DayOfWeek,   -- 1=Sunday, 7=Saturday
       DAYOFMONTH(JoinDate) AS DayOfMonth,
       DAYOFYEAR(JoinDate)  AS DayOfYear
FROM Students
WHERE StudentID = 1;

Result for 2024-01-10: DayOfWeek=4 (Wednesday), DayOfMonth=10, DayOfYear=10

3. Date Arithmetic — Adding and Subtracting Dates

DATE_ADD() and DATE_SUB()

DATE_ADD(date, INTERVAL value unit) adds a specified time interval to a date. DATE_SUB() subtracts one.

SELECT JoinDate,
       DATE_ADD(JoinDate, INTERVAL 30 DAY)   AS Plus30Days,
       DATE_ADD(JoinDate, INTERVAL 6  MONTH) AS Plus6Months,
       DATE_ADD(JoinDate, INTERVAL 1  YEAR)  AS Plus1Year,
       DATE_SUB(JoinDate, INTERVAL 7  DAY)   AS Minus7Days
FROM Students
WHERE StudentID = 1;

Result for 2024-01-10:

Valid Interval Units

4. DATEDIFF()

DATEDIFF(date1, date2) returns the number of days between two dates. A positive result means date1 is later than date2.

-- How many days has each student been enrolled?
SELECT StudentName, JoinDate,
       DATEDIFF(CURDATE(), JoinDate) AS DaysEnrolled
FROM Students;

Result: If today is 2024-12-01 and Ravi joined on 2024-01-10, the result is 326 days.

-- Find students enrolled for more than 300 days
SELECT StudentName, JoinDate
FROM Students
WHERE DATEDIFF(CURDATE(), JoinDate) > 300;

5. TIMESTAMPDIFF()

TIMESTAMPDIFF(unit, start_date, end_date) returns the difference between two dates in the specified unit — useful for calculating age in years.

-- Calculate age of each student
SELECT StudentName, BirthDate,
       TIMESTAMPDIFF(YEAR, BirthDate, CURDATE()) AS Age
FROM Students;

Result:

6. DATE_FORMAT()

DATE_FORMAT(date, format_string) formats a date into a custom display format using format specifiers.

SELECT JoinDate,
       DATE_FORMAT(JoinDate, '%d %M %Y')      AS Formatted1,
       DATE_FORMAT(JoinDate, '%D %b %Y')      AS Formatted2,
       DATE_FORMAT(JoinDate, '%m/%d/%Y')      AS USFormat,
       DATE_FORMAT(JoinDate, '%d-%m-%Y')      AS IndiaFormat
FROM Students
WHERE StudentID = 1;

Result for 2024-01-10:

Common DATE_FORMAT Specifiers

7. STR_TO_DATE()

STR_TO_DATE(string, format) converts a formatted date string into a proper SQL date value. This is useful when importing data where dates are stored as text.

-- Convert '15-06-2024' text into a proper DATE value
SELECT STR_TO_DATE('15-06-2024', '%d-%m-%Y') AS ProperDate;

Result: 2024-06-15 (stored as a proper DATE value)

Practical Use Cases

Find Students Who Joined in a Specific Month

SELECT StudentName, JoinDate
FROM Students
WHERE MONTH(JoinDate) = 2 AND YEAR(JoinDate) = 2024;

Result: Priya Mehta and Arjun Nair (both joined in February 2024).

Find Students Whose Birthday Is This Month

SELECT StudentName, BirthDate
FROM Students
WHERE MONTH(BirthDate) = MONTH(CURDATE());

Find Students Who Joined in the Last 60 Days

SELECT StudentName, JoinDate
FROM Students
WHERE JoinDate >= DATE_SUB(CURDATE(), INTERVAL 60 DAY);

Quick Reference Table

Key Points to Remember

• SQL stores dates in YYYY-MM-DD format internally. DATE_FORMAT() is used only for display.
• DATEDIFF() measures the difference in days only. Use TIMESTAMPDIFF() for other units like years or months.
• EXTRACT() works across most database systems, while YEAR() / MONTH() / DAY() are MySQL-specific shortcuts.
• Date functions can be used in WHERE clauses to filter by date ranges or specific date parts.

Summary

SQL date and time functions are essential tools for any application that stores time-sensitive data. They handle everything from getting the current moment with NOW(), to extracting date parts with YEAR() and MONTH(), calculating differences with DATEDIFF() and TIMESTAMPDIFF(), and formatting for display with DATE_FORMAT(). Mastering these functions is key to building queries that work with real-world date-driven data.