SQL Date and Time Functions

SQL provides a rich set of date and time functions for working with date and time values. 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 functions are some of the most commonly used functions in real-world SQL, because almost every application records when things happened — orders placed, students enrolled, events scheduled.

The Reference Table

Date Function Reference

Get Current Date and Time

MySQL Query

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

MS SQL Server Query

SELECT GETDATE()                   AS CurrentDateTime,
       CAST(GETDATE() AS DATE)     AS TodayDate,
       CAST(GETDATE() AS TIME)     AS CurrentTime;

Extract Year, Month, Day

These three functions are identical in both databases.

MySQL Query

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

MS SQL Server Query

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

Get Day Name and Month Name

MySQL Query

SELECT JoinDate,
       DAYNAME(JoinDate)   AS DayName,
       MONTHNAME(JoinDate) AS MonthName
FROM Students;

MS SQL Server Query

-- DAYNAME and MONTHNAME do not exist in MS SQL Server. Use DATENAME() instead.
SELECT JoinDate,
       DATENAME(weekday, JoinDate) AS DayName,
       DATENAME(month,   JoinDate) AS MonthName
FROM Students;

Add and Subtract Dates

MySQL Query

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

MS SQL Server Query

-- DATE_ADD and DATE_SUB both become DATEADD(unit, n, date)
-- Use a negative number for subtraction
SELECT JoinDate,
       DATEADD(day,   30, JoinDate)  AS Plus30Days,
       DATEADD(month,  6, JoinDate)  AS Plus6Months,
       DATEADD(day,   -7, JoinDate)  AS Minus7Days
FROM Students
WHERE StudentID = 1;

DATEDIFF — Difference Between Two Dates

Important: The argument order is different between the two databases.

MySQL Query

-- MySQL: DATEDIFF(later_date, earlier_date) -- always returns days
SELECT StudentName, JoinDate,
       DATEDIFF(CURDATE(), JoinDate) AS DaysEnrolled
FROM Students;

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

MS SQL Server Query

-- MS SQL Server: DATEDIFF(unit, start_date, end_date) -- argument order reversed
SELECT StudentName, JoinDate,
       DATEDIFF(day, JoinDate, GETDATE()) AS DaysEnrolled
FROM Students;

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

Calculate Age in Years

MySQL Query

-- TIMESTAMPDIFF calculates the difference in the specified unit
SELECT StudentName, BirthDate,
       TIMESTAMPDIFF(YEAR, BirthDate, CURDATE()) AS Age
FROM Students;

MS SQL Server Query

-- TIMESTAMPDIFF does not exist. Use DATEDIFF with the 'year' unit.
SELECT StudentName, BirthDate,
       DATEDIFF(year, BirthDate, GETDATE()) AS Age
FROM Students;

Format Date for Display

MySQL Query

SELECT JoinDate,
       DATE_FORMAT(JoinDate, '%d %M %Y') AS LongFormat,
       DATE_FORMAT(JoinDate, '%d-%m-%Y') AS IndiaFormat,
       DATE_FORMAT(JoinDate, '%m/%d/%Y') AS USFormat
FROM Students;

MS SQL Server Query

-- DATE_FORMAT does not exist. Use FORMAT() with .NET format strings.
SELECT JoinDate,
       FORMAT(JoinDate, 'dd MMMM yyyy') AS LongFormat,
       FORMAT(JoinDate, 'dd-MM-yyyy')   AS IndiaFormat,
       FORMAT(JoinDate, 'MM/dd/yyyy')   AS USFormat
FROM Students;

Convert a String to a Date

MySQL Query

-- Convert a date string in dd-mm-yyyy format into a proper DATE value
SELECT STR_TO_DATE('15-06-2024', '%d-%m-%Y') AS ProperDate;

MS SQL Server Query

-- TRY_PARSE converts a string to a date (SQL Server 2012+)
SELECT TRY_PARSE('15-06-2024' AS DATE USING 'en-GB') AS ProperDate;

-- Alternative using CONVERT with style code 103 = dd/mm/yyyy
SELECT CONVERT(DATE, '15/06/2024', 103) AS ProperDate;

Practical Filter Queries

MySQL Query

-- Students who joined in February 2024
SELECT StudentName, JoinDate FROM Students
WHERE MONTH(JoinDate) = 2 AND YEAR(JoinDate) = 2024;

-- Students whose birthday is this month
SELECT StudentName, BirthDate FROM Students
WHERE MONTH(BirthDate) = MONTH(CURDATE());

-- Students who joined in the last 60 days
SELECT StudentName, JoinDate FROM Students
WHERE JoinDate >= DATE_SUB(CURDATE(), INTERVAL 60 DAY);

MS SQL Server Query

-- Students who joined in February 2024 (MONTH and YEAR are identical)
SELECT StudentName, JoinDate FROM Students
WHERE MONTH(JoinDate) = 2 AND YEAR(JoinDate) = 2024;

-- Students whose birthday is this month
SELECT StudentName, BirthDate FROM Students
WHERE MONTH(BirthDate) = MONTH(GETDATE());

-- Students who joined in the last 60 days
SELECT StudentName, JoinDate FROM Students
WHERE JoinDate >= DATEADD(day, -60, GETDATE());

Key Points

• MySQL uses NOW(); MS SQL Server uses GETDATE().
• MySQL uses CURDATE(); MS SQL Server uses CAST(GETDATE() AS DATE).
• YEAR(), MONTH(), DAY() are identical in both databases.
• MySQL uses DAYNAME() / MONTHNAME(); MS SQL Server uses DATENAME(weekday, d) / DATENAME(month, d).
• MySQL uses DATE_ADD(d, INTERVAL n unit); MS SQL Server uses DATEADD(unit, n, d).
• MySQL's DATEDIFF(later, earlier) returns days only. MS SQL Server's DATEDIFF(unit, earlier, later) supports any unit — and the argument order is reversed.
• MySQL uses DATE_FORMAT(d, '%d %M %Y'); MS SQL Server uses FORMAT(d, 'dd MMMM yyyy').