Getting the correct hour value with datediff-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

 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.