Getting the correct hour value with datediff

Question

I'm trying to use the datediff function in SQL Server. I'm querying two datetime columns in the same table. I'm trying to get the results where I get hours and minutes in decimal points.

As an example I have this query:

Select 
    startdatetime, enddatetime, 
    datediff(minutes, startdatetime, enddatetim) / 60 as 'Hrs worked' 
from table1

The results will be something like:

startdatetime                      |  enddatetime                          |  Hrs. worked
2019-02-28 06:00:00.0000000 -08:00    2019-02-28 07:15:00.0000000 -08:00      1

Clearly the difference between startdatetime and enddatetime should be 1.25 not just 1.

Is there anyway to show the decimal value (1.25)?

I also tried:

Select 
    StartDateTime, EndDateTime, 
    Convert(decimal(3), (Datediff(minute, StartDateTime, EndDateTime))) / 60 AS 'Hrs Worked' 
From Table1

and I do get the Hrs. worked as '1.250000', but I'm unable to drop the trailing zero.

If I use decimal(2) instead of decimal(3), I get this error:

Arithmetic overflow error converting int to data type numeric

Any suggestions?

A trailing zero doesn't change the value of a number? – Dale K Sep 28 '21 at 05:29 — Dale K, Sep 28 '21 at 05:29

Jack Liu · Answer 1 · 2021-09-28T03:59:13.243

0

 DECLARE @startdate DATETIME = '2019-02-28 06:00:00';
    DECLARE @enddate DATETIME = '2019-02-28  07:15:00';
    
    SELECT CAST(CAST(DATEDIFF(MINUTE, @startdate, @enddate) AS DECIMAL) /  60 AS DECIMAL(10, 2))

maybe you also need this post to properly format it.

edited Sep 28 '21 at 03:59

answered Sep 28 '21 at 03:14

Jack Liu

1
2

score 0 · Answer 2 · answered Sep 28 '21 at 03:46

0

DATEDIFF returns an INT value (here: 75), and dividing that by 60 uses integer division, so you won't get any fractions of an int (basically answering the question: "how many times can '60' be completely contained in the value in question - here '75' - and that's just '1').

If you want the fractional hours - just divide by 60.0 to get "normal" decimal division:

SELECT
    StartDateTime, EndDateTime, 
    DATEDIFF(minute, StartDateTime, EndDateTime) / 60.0 AS 'Hrs Worked' 
FROM
    Table1

This will return the expected 1.25 as your value

answered Sep 28 '21 at 03:46

marc_s

732,580
175
1,330
1,459

1

Thanks guys (Jack Liu & marc_s), actually the solution was in combination to both your posting. I had to define the decimal to be (10, 2) and divide by 60.0. Without both solutions, the results will be a numeric with about five integer numbers (i.e. 1.25000). By combining both solutions I finally got the result 1.25. Thanks guys. Very helpful. – R. Pen Sep 28 '21 at 17:29

score 0 · Answer 3 · answered Sep 28 '21 at 17:33

The solution was in combination from both Jack Liu & marc_s suggestion.

I needed to also define the decimal and explicitly set 60.0.

Here is an example of the correct format in what I am trying to do.

Select StartDateTime, EndDateTime, CAST(Datediff(minute, StartDateTime, EndDateTime) / 60.0 AS Decimal(10, 2))'Hrs Worked' From Table1

Getting the correct hour value with datediff

3 Answers3