?
Solved

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

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Steps to solve SSRS SQL 2008 R2 User Access Control (UAC) Permission Error With the introduction of SQL Server 2008 R2 and Vista (Windows 7 as well) came new enhanced security features. One of the features included was User Access Control (UAC) t…
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
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

650 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