CODEWITHSUNDEEP
sqlsql-servert-sqldatetimedatediff
EJF
EJF

Reputation: 461

Calculate difference in hours between two datetime values

I'm working on a query that deals with the details behind a series of electronic documents. Each document has a CreateDate and a CompletedDate, both of which are in datetime format. My goal is to calculate the number of hours that elapse between these two dates.

SELECT
DocID,
CreateDate,
CompletedDate,
CONVERT(DECIMAL(10,2),DATEDIFF(HOUR,CreateDate,CompletedDate)) AS DiffHours

FROM Documents

It's a simple query, and it works perfectly fine. However, I end up with results like this:

DocID   CreateDate                  CompletedDate               DiffHours
--------------------------------------------------------------------------
0001    2021-07-01 08:34:28.130     2021-07-01 08:58:14.633     0.00
0002    2021-07-01 10:33:03.157     2021-07-02 08:37:14.120     22.00

The conversion to decimal technically works, but it's still only displaying the DiffHours result as a rounded number. For example, for that first document with ID 0001, a full hour hasn't elapsed between the two times, but I still would have expected the result to show as 0.42 or so rather than rounding down to 0. Likewise, I would have expected the second document to show as 22.[something] rather than just the flat 22.00.

Is there a way to force the calculation to be more exact? Again, technically the query works the way it is, but if I can't display the correct fraction of the hour, then it's not going to be as useful as I'd like it to be.

Upvotes: 0

Views: 105

Answers (2)

Stu
Stu

Reputation: 32579

for greater accuracy you need to use higher precision values, eg the difference in seconds.

For example

declare @createdate datetime='20210701 08:34:28.130', @CompletedDate datetime='20210701 08:58:14.633'

select Convert(decimal(15,2),DateDiff(second, @createdate,@completeddate)/60.0/60.0)
select Convert(decimal(15,4),DateDiff(second, @createdate,@completeddate)/60.0/60.0)

Output

0.40

0.3961

Upvotes: 3

John Cappelletti
John Cappelletti

Reputation: 81930

Calculate the delta in MINUTES and then convert into hours by dividing by 60.

Note the 60.0 ... this will prevent integer division which will return an integer.

Example

Declare @YourTable Table ([DocID] varchar(50),[CreateDate] datetime,[CompletedDate] datetime)
Insert Into @YourTable Values 
 ('0001','2021-07-01 08:34:28.130','2021-07-01 08:58:14.633')
,('0002','2021-07-01 10:33:03.157','2021-07-02 08:37:14.120')
 
Select * 
      ,Diff = convert(decimal(15,2),datediff(MINUTE,[CreateDate],[CompletedDate]) / 60.0)
from @YourTable

Results

DocID   CreateDate              CompletedDate            Diff
0001    2021-07-01 08:34:28.130 2021-07-01 08:58:14.633  0.40
0002    2021-07-01 10:33:03.157 2021-07-02 08:37:14.120  22.07

Upvotes: 1

Related Questions