[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2009-05-06
2
Medium Priority
?
2,429 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
Hi All, I am here to write a simple article to move SSRS (SQL Server Reporting Services) reports from one server to another. When I have faced the same issue to move reports those were developed by developer on development server and now need to …
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…
Suggested Courses
Course of the Month17 days, 17 hours left to enroll

830 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