SQL > SQL Date Functions > Datediff Function

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:

datepartabbreviation
yearyy, yyyy
quarterqq, q
monthmm, m
dayofyeardy, y
daydd, d
weekwk, ww
hourhh
minutemi, n
secondss, s
millisecondms
microsecondmcs
nanosecondns
TZoffsettz
ISO_WEEKisowk, 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.

List of SQL Date Functions

Function NameUsage
DATEADDAdds an interval to a date value in SQL Server
DATEDIFFCalculates the difference between two dates in MySQL and SQL Server
DATEPARTExtracts a specific part of a date/time value in SQL Server
GETDATERetrieves database time in SQL Server
SYSDATERetrieves database time in Oracle and MySQL

Next: SQL DATEPART




Copyright © 2016   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact