SQLTeam.com | Weblogs | Forums

Convert Seconds into Hours (Cast)

Hi All,
My following SQL calculating the difference between startdate and enddate into Second shown below:

     `DATEDIFF(ss, tblVisit.DateServiceStart, tblVisit.DateServiceEnd) AS DurationInSeconds`

but when I divide by 3600 to get the Hours its only show 0.00 instead of 1.18

Blockquote
,DATEDIFF(ss, tblVisit.DateServiceStart, tblVisit.DateServiceEnd) AS DurationSeconds
,DATEDIFF(mi, tblVisit.DateServiceStart, tblVisit.DateServiceEnd) AS TimeOnSite_Min
,(DATEDIFF(mi, tblVisit.DateServiceStart, tblVisit.DateServiceEnd)*60)/3600 AS TimeOnSite_hh
,CAST((DATEDIFF(ss, tblVisit.DateServiceStart, tblVisit.DateServiceEnd))/3600 as decimal(2,2)) AS DurationHrs

The output shown below from above SQL

DurationSeconds DurationHrs
1620 0.00
2640 0.00
0 0.00
Expecting the answer 0.45 for 1st rows , 0.73

Your dividing and integer by an integer and SQL returns that as an integer so converting 0.45 to an integer is 0 and converting that to decimal is 0.00. Try dividing by 3600.00 in order to return a decimal 0.45