Wednesday, September 28, 2011

Difference between two dates in SQL Server


If we want to get the difference between two dates in SQL Server then we need to make use of DATEDIFF function. Showed few variations using DATEDIFF in my forum response here. So thought of logging in my blog as well for future reference :)



DECLARE @StartDate DATETIME
SET @StartDate = '2011-01-01 10:15:00.000'


Declare @EndDate DATETIME
SET @EndDate = '2011-01-01 11:35:00.000'

--To get only Hours
SELECT DATEDIFF(hh, @StartDate,@EndDate ) AS [Hours];

--To get the result in Minutes
SELECT CAST(DATEDIFF(ss, @StartDate, @EndDate) AS DECIMAL(10, 0)) / 60 AS [Minutes];

--To get both Hours and Minutes
SELECT DATEDIFF(hh, @StartDate, @EndDate) AS [Hours],
DATEDIFF(mi,DATEADD(hh,DATEDIFF(hh, @StartDate, @EndDate),@StartDate),@EndDate) AS [Minutes];

No comments: