DateDiff function in SQL Server 2008
In this article I describe about datediff function in sql server.
Introduction
The DATEDIFF function is used to return the difference between two dates. It calculates the time between start and end time.
Syntax
The syntax for using datediff() built-in function is given below:
DATEDIFF (datepart,startdate,enddate)
|
where,
DatePart parameter is used to calculate the difference. The datepart can be one of the following:
DatePart |
Symbol |
Year |
yy |
Quarter of the year |
qq |
Day |
dd |
Day of the year |
dy |
Month |
mm |
Week |
wk |
Day of the week |
dw |
Second |
ss |
Miliseconds |
ms |
Hour |
hh |
Examples
Get the number of years between two dates.
Select datediff (YY,'2008-06-05','2012-08-05')AsYear
|
Output:
Get the number of days between two dates.
Select datediff (D,'2008-06-05','2012-08-05')AsYear
Select datediff (DAYOFYEAR,'2008-06-05','2012-08-05')AsYear
Select datediff (DD,'2008-06-05','2012-08-05')AsYear
Select datediff (DAY,'2008-06-05','2012-08-05')AsYear
|
Output:
Get the number of minutes between two dates.
Select datediff (MI,'2008-06-05','2012-08-05')as min
Select datediff (MINUTE,'2008-06-05','2012-08-05')as min
|
Output:
Get the number of seconds between two dates.
Select datediff (S,'2008-06-05','2012-08-05')as seconds
Select datediff (SECOND,'2008-06-05','2012-08-05')as seconds
Select datediff (SS,'2008-06-05','2012-08-05')as seconds
|
Output: