Solved

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

Posted on 2009-05-06
2
2,329 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

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…
How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS. Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some steps…
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…

730 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