SQL Decimal Time Conversion

Last Post 10 Jan 2008 05:42 PM by tsilcyc. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
tsilcyc
New Member
New Member

--
10 Jan 2008 06:18 AM
The following is my statement but it shows time as a decimal. I need time to show as HH:MM. I searched the forum and couldn't find any examples so forgive me if this has already been answered somewhere.
----------
select event_id, sum(distance_in_miles) as this_month,convert(float,sum(time_h))+ convert(float,sum(time_m)/60) +
convert(float,sum(time_s)/3600) as time_month
from event_transaction
where event_date between DATEADD(day,-(DAY(GETDATE())),GETDATE()) and GETDATE()
and contact_id=45 and validity='Y'
group by event_id
order by event_id
----------
tsilcyc
New Member
New Member

--
10 Jan 2008 01:09 PM
After re-reading this, I didn't explain it correctly. I'm trying to get a duration in HH:MM from the sum.
tsilcyc
New Member
New Member

--
10 Jan 2008 05:42 PM
event_ID | this_month | time_month

01 | 00 | 0.5
02 | 23.11 | 3.2380550000000001
03 | 3.50 | 1.75
04 | 159.96 | 10.024722000000001

This is my output from the above query.

I want the times to report this:

0:30:00
3:14:17
1:45:00
10:01:29



SQLUSA
New Member
New Member

--
10 Jan 2008 09:57 PM
Try this:

select event_id, sum(distance_in_miles) as this_month,
convert(varchar,dateadd(ss, sum(time_h)*3600+sum(time_m)*60+sum(time_s), '2008-01-01 00:00:00.000' ),108)
as time_month
from event_transaction
where event_date between DATEADD(day,-(DAY(GETDATE())),GETDATE()) and GETDATE()
and contact_id=45 and validity='Y'
group by event_id
order by event_id

Let us know.


Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005grandslam/ The Best SQL Server 2005 Training in the World!
tsilcyc
New Member
New Member

--
11 Jan 2008 03:39 AM
YOU ROCK!!! Thank you so much!!!
You are not authorized to post a reply.

Acceptable Use Policy