Post reply

Need to get Time diff between 2 dates

  • May 13, 2007 at 2:15 am

    #195724

    Hi,

    I've tried a few combinations but i can't get it going, ideally i'd like an output of "x Hours y Minutes" but i'd settle for "Hh:ss"

    i've tried both the following...

    =Format(CDate(First(Fields!DateTimeLeft.Value)-First(Fields!DateTimeArrived.Value)),"HH:mm:ss")

    =FormatDateTime(First(Fields!DateTimeLeft.Value)-First(Fields!DateTimeArrived.Value),"HH:mm")

    =CDate(DateDiff(DateInterval.Minute,First(Fields!DateTimeArrived.Value),First(Fields!DateTimeLeft.Value)),"HH:mm")

    but get conversion errors or #Error in the report

    any help appreciated

    Steve

  • May 13, 2007 at 8:19 pm

    #706077

    Are you sure the script you posted is SQL, not VB?

    _____________
    Code for TallyGenerator

  • May 13, 2007 at 9:49 pm

    #706086

    Sergiy, The language for formulas in Reporting Services is VB.Net

    Steve, Not sure where you're using the value, but why not do you calculation in SQL anyway, that reduces the work the report has to do.

     

    --------------------
    Colt 45 - the original point and click interface

  • May 14, 2007 at 6:31 am

    #706193

    ok, how would i code it in the sql ??

  • May 14, 2007 at 8:13 am

    #706231

    try this: datediff(minute, date1, date2) as mins, datediff(second, date1,date2) as secs as the function calls for calculating the difference between dates, in SQL. Place it in the select statement and you can either assign to variables or store in some other way. then you only need to format the output, after the 'hard' datetime calculation is complete. beware that it calculates the total time difference - if these datetimes are not the same calendar day, you may need to modulo the results.

  • May 14, 2007 at 12:09 pm

    #706299

    yep, that was the way to do it, in the SQL, makes sense now, for info the code i actually used was...

    CASE WHEN DATEDIFF(SECOND, Attendance.DateTimeArrived, Attendance.DateTimeLeft) / 3600 = 0 THEN '' ELSE CAST(DATEDIFF(SECOND, Attendance.DateTimeArrived, Attendance.DateTimeLeft) / 3600 AS VARCHAR(2)) + ' Hours ' END + RIGHT('0' + CAST(DATEDIFF(SECOND, Attendance.DateTimeArrived, Attendance.DateTimeLeft) % 3600 / 60 AS VARCHAR(2)) + ' mins', 7) AS SiteTime

    which gives me a nice "X Hours Y mins" output

    thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply