Solved

How can I format timespan in field express to show just HH:mm

Posted on 2009-05-06
2
2,277 Views
Last Modified: 2012-05-06
I have a report that I have to derive the DutyHours from two datetime fields (startdate & end date).  datediff(minute, min(s.arriveDateTime), max(s.departDateTime)) 'hours',  I needed the total time to show in hour and minutes as 10.05 instead of 10.09 (decimal).  I fixed this with  convert(varchar(2), x.hours / 60) + ':' + convert(varchar(2), x.hours % 60).

Now my problem is...how do I get my total hours.  I can't SUM a string.  I tried to use the timespan.parse in my field expression and that works great except that I don't need the seconds to show.  Does someone know how to format the timespan option to show just HH:ss?
0
Comment
Question by:swtjen01
2 Comments
 
LVL 63

Expert Comment

by:Zvonko
ID: 24317749
You make it too complicated.
Nobody presures you to have only one variable.
Make TWO variables: one for formated user readbale output and the other as hidden variable only for internal sum calculation.
0
 

Accepted Solution

by:
swtjen01 earned 0 total points
ID: 24369525
Here is how I came up with the solution and looking back I didn't explain my problem well.  My problem was that I couldn't get my time to change from decimal to HH:mm and have it SUM correctly.  I tried to use the timespan function but I couldn't figure out how to format it so I ended up doing this to solve my problem:

I changed my code and used a subselect to get the derived hours:  datediff(minute, min(s.arriveDateTime), max(s.departDateTime)) 'hours' .  

In select statment:  sum(coalesce(x.hours,0)) 'hours',

To get the time to display correctly in my text field I used the expression below for the detail row:
=Floor(Fields!hours.Value /60) & ":" & format(fields!hours.value Mod 60, "00")

And this in the footer row to get the SUM:
=Floor(sum(Fields!hours.Value)/60) & ":" & Format(Sum(Fields!hours.Value) Mod 60 ,"00")

Hope this helps somebody out...
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

After much searching across the internet I have found that you could not set the name of the file you were attaching to dynamic report subscriptons in Microsoft Reporting Services. I did manage to find one article showing you how your could make a s…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now