SQL DATEDIFF | Calculate Date Differences in SQL

SQL > SQL Date Functions > Datediff Function

The DATEDIFF function is used to calculate the difference between two dates, and is used in MySQL and SQL Server.

The syntax for this date function is different between these two databases, so each one is discussed below:

DATEDIFF Syntax in MySQL

The syntax for the DATEDIFF function in MySQL is

DATEDIFF (expression1, expression2)

where the data type of <expression1> and <expression2> is either DATE or DATETIME. The result is <expression1> - <expression2> and the unit is in days.

DATEDIFF Examples in MySQL

Example 1

The SQL statement,

SELECT DATEDIFF ('2000-01-10', '2000-01-05');

produces the following result:

This is because 2000-01-10 is 5 days after 2000-01-05.

Example 2

The SQL statement,

SELECT DATEDIFF ('2000-01-05 00:00:00', '2000-01-04 23:59:59');

produces the following result:

Even though the two DATETIME values differ only by 1 second, the DATEDIFF function returns 1, meaning 1 day. This is because MySQL ignores everything after the date (hour, minute, second).

DATEDIFF Syntax in SQL Server

The syntax for the DATEDIFF function in SQL Server is

DATEDIFF (datepart, expression1, expression2)

where the data type of <expression1> and <expression2> is some type of date, time, or datetime. The result is <expression2> - <expression1>. datepart can be one of the following:

datepart	abbreviation
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns
TZoffset	tz
ISO_WEEK	isowk, isoww

DATEDIFF returns an integer in SQL Server. It only compares the values down to the lowest level of the given datepart, and does not look at anything that is lower. So, if "datepart" is month, the DATEDIFF function will only look at the Year and Month fields in calculating the difference between the two. All other date fields (such as day, hour, minute, second, milisecond) are ignored.

DATEDIFF Examples in SQL Server

Example 1

The SQL statement

SELECT DATEDIFF (day, '2000-01-10 00:05:00.000','2000-01-05 05:05:00.000');

produces the following result:

-5

Example 2

The SQL statement

SELECT DATEDIFF (month, '2000-01-31 23:59:59.000','2000-02-01 00:00:00.000');

produces the following result:

Even though the actual difference between the two datetime values is 1 second, the DATEDIFF function returns 1 when we ask for the difference in months. This is because SQL Server only compares the year and month fields. All other fields (day, hour, minute, second, milisecond) are ignored.

List of SQL Date Functions

Function Name	Description
DATEADD	Adds an interval to a date value in SQL Server.
DATEDIFF	Calculates the difference between two dates in MySQL and SQL Server.
DATEPART	Extracts a specific part of a date/time value in SQL Server.
GETDATE	Retrieves database time in SQL Server.
SYSDATE	Retrieves database time in Oracle and MySQL.
EXTRACT	Retrieves a certain component of a date or timestamp value.

Next: SQL DATEPART

This page was last updated on October 12, 2024.

SQL DATEDIFF Function

DATEDIFF Syntax in MySQL

DATEDIFF Examples in MySQL

Example 1

Example 2

DATEDIFF Syntax in SQL Server

DATEDIFF Examples in SQL Server

Example 1

Example 2

List of SQL Date Functions

AdBlock Detected!