Post reply

Showing Time in [h]:mm:ss format

  • February 5, 2010 at 10:37 am

    #386260

    Hi,

    We are trying to create a report where difference of two date values as Hours:Minutes:Seconds. We used HH:mm:ss with Time as format on the column. It works fine if difference between two dates is less than 24 hours but if difference is more than 24 hours then this formatting ignores that in representation.

    Interesting bit is when we export that report in excel and apply [h]:mm:ss as Time format, it shows all days converted into hours. So for example for a difference of two dates equal to

    02/01/1900 07:48:34, here are results

    On report with HH:mm:ss the value is 07:48:34

    Excel export copy of report (with formatting [h]:mm:ss applied in excel) the value is 55:48:34

    When I try to apply this customer format in SSRS using Textbox Properties, it gives me error that

    [h]:mm:ss is not a valid token syntax.

    Any idea how we can have same excel format [h]:mm:ss applied on the report without asking users to manually do it each time they export the report?

    Kind regards

    Ahmad

  • February 5, 2010 at 12:12 pm

    #1114701

    i think something like this might help you build what you are after?:

    results:

    Years Months Days Hours Minutes Seconds

    ----------- ----------- ----------- ----------- ----------- -----------

    48 2 3 22 38 44

    example:

    declare @d1 datetime,

    @d2 datetime

    SET @d1='19621211 16:32:59.000'

    SET @d2=getdate()

    SELECT

    DATEDIFF(year,@d1,@d2) AS Years,

    DATEDIFF(month,@d1,@d2)% 12 AS Months,

    DATEDIFF(day,@d1,@d2)% 30 AS Days,

    DATEDIFF(hour,@d1,@d2)%24 AS Hours,

    DATEDIFF(minute,@d1,@d2)%60 AS Minutes,

    DATEDIFF(second,@d1,@d2)%60 AS Seconds

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 9, 2010 at 8:09 am

    #1116145

    Lowell,

    Thanks for your reply. However, this not something that I am looking for. I need timespan in one column and not in multiple and formatted as time in reports.

    As you know when we use DateDiff, the result value reference back from 01/01/1900 00:00:00:000. So that provides us timespan between the two dates. I want to present this timespan

    - in hours:minutes:seconds. So total years/months/days converted to hours,

    - any minutes values presented as minutes

    - any seconds value presented as seconds

    Microsoft excel allows me to see a date like 02/01/1900 07:48:56 as 55:48:56 if I apply time format '[h]:mm:ss'.

    If I 10 rows in excel, it adds them up correctly as well.

    But reporting services do not provide a time format like this. So I have no way to achieve this as time format. I can produce such format using CLR .NET function (which i call in my SP) but then output is String and not Time. Once I export it, Excel does like to add up the rows as it considers them Strings.

    My question is that we have a time format in excel [h]:mm:ss but this format is not available in reporting services. Is there anyway that this format can be applied, so that column format stays as Time and add up rows work correctly?

    Kind regards

  • February 23, 2010 at 12:00 pm

    #1123809

    Hi,

    I think we can create custom code to get this done..

    Thanks

    veeren.

    Thanks & Regards,
    Veeren.
    Ignore this if you feel i am Wrong. 😉

Viewing 4 posts - 1 through 3 (of 3 total)

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