Solved

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

Posted on 2009-05-06
2
2,297 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Greater than or equal to 2005? 3 46
SSRS Calculation of two fields not working 7 56
Java / Linux and Regular Expressions 11 80
SQL 2012 R2 Express report problem 2 89
Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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…

825 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