SQL DATEDIFF

The SQL DATEDIFF() function is used to get the date/time difference of 2 date expressions. It is supported by both SQL Server and MySQL, but the syntax is different.

SQL CASE Syntax

MySQL:

DATEDIFF(Date1, Date2)

Date1 and Date2 can be any valid date/time expression, but only the date part will be calculated, it will return the difference in days.

SQL Server:

DATEDIFF(datepart, Date1, Date2)

Date1 and Date2 can be any valid date/time expression, and datepart can be anyone 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 sowk, isoww

Note: in MySQL the result = Date1 – Date2, but in SQL Server the result = Date2 – Date1.

SQL DATEDIFF Examples

MySQL Examples:

SELECT DATEDIFF('2011-09-05 13:00:00','2011-09-08')

The result will be: -3

SELECT DATEDIFF('2011-10-02','2011-09-08')

The result will be: 24

SELECT DATEDIFF('2011-09-20 8:12:00','2011-09-20 10:23:00')

The result will be: 0

SQL Server Examples:

SELECT DATEDIFF(yyyy, '2010-09-01', '2011-07-01')

The result will be: 1

SELECT DATEDIFF(hh, '2011-09-01', '2011-07-01')

The result will be: -1488

SELECT DATEDIFF(hh, '2011-09-20 8:12:00', '2011-09-20 10:23:00')

The result will be: 2

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>