Showing Time in [h]:mm:ss format

Question

Post reply

Showing Time in [h]:mm:ss format

id1970

SSC Veteran

Points: 231
More actions
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

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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!

id1970 SSC Veteran Points: 231 More actions · Answer 2

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

veeren4urs SSCrazy Points: 2763 More actions · Answer 3

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. 😉