CODEWITHSUNDEEP
sqlsql-serverdecimaldatediff
Yohan Wijayanto
Yohan Wijayanto

Reputation: 25

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.

Upvotes: 1

Views: 5094

Answers (2)

Luong.Khuc
Luong.Khuc

Reputation: 26

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

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81930

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

Upvotes: 4

Related Questions