get datediff hours in decimal sql server

问题:

i need query to get total hours in decimal value

this is my query so far:

declare @start as time;
declare @end as time;
declare @total as decimal(18, 2);

set @start = '17:00:00'
set @end = '18:30:00'

set @total = datediff(minute, @start, @end)/60

print @total

the query give me integer value, although the @total parameter is a decimal. I don't know how to get the decimal value.

please help me, and thank you in advance.

回答1:

Try divide by 60.0. This will provide the required precision

An int divided by an int will return an int. To circumvent this, simply make either the numerator or denominator into a float.

Example

declare @start as time;
declare @end as time;
declare @total as decimal(18, 2);

set @start = '17:00:00'
set @end = '18:30:00'

set @total = datediff(minute, @start, @end)/60.0

print @total

Returns

1.50

回答2:

DateDiff always return int, so take the DateDiff in minute, and then divide by 60.0. The decimal point is required.

set @total = datediff(minute, @start, @end)/60.0