Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
matt_calhoon
Posting Yak Master
235 Posts |
 Posted - 2003-02-04 : 03:01:03
|
| Hi there,I have a column which stores hours & minutes (HH.MM) as smallmoney datatype. each data entry would be similar to this format: 1.45 (represents 1 hour and 45 minutes).I need to return the total no of hours from this field using SUM but need to convert the time representation to a decimal value. So for example:1.45 (1 hour and 45 minutes)3.20 (3 hours and 20 minutes)2.40 (2 hours and 40 minutes)would add up to 7.05 but I need it to add up to 7.45 (hours & minutes) then finally convert this to a decimal.Any help appreciated.thanks - matt |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-02-04 : 03:50:47
|
| I'm sure someone will find an easier way butselect convert(decimal(4,2),convert(varchar(10),hrs + round(mins/60,0,1)) + '.' + convert(varchar(2),mins%60 ))from(select hrs = sum(convert(int,fld)) , mins = convert(int,sum(fld - convert(int,fld))*100)from tbl) as a==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
convert time value to decimal