The DATEDIFF function is used to calculate the difference between two days, 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:

MySQL:

The usage 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>.

Example: The SQL statement

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

yields the following result:

5

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

SQL Server:

The usage 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

Example: The SQL statement

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

yields the following result:

-5

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

Link to this page: If you find this page useful, we encourage you to link to this page. Simply copy and paste the code below to your website, blog, or profile.

<a href="http://www.1keydata.com/sql/sql-datediff.html">SQL DATEDIFF Function</a>