Prev: IIS hangs when accessing SQL Reports
Next: Custom Assembly error in SSRS 2008 Options
From: dataGirl on 5 Apr 2010 11:21
I'm trying to duplicate an Excel date/time format in Reporting Services 2005.
On the Excel sheet, the difference between two dates is displayed formatted
as "150:58:12" and then as "6.29". (12/1/2009 9:41:49 AM - 11/25/2009
2:43:37 AM). Is there a way to format the date/time in Reporting Services to
do this?
From: Uri Dimant on 6 Apr 2010 03:28
dataGirl
You can format dates with .NET formatting yyyMMdd os something like that


"dataGirl" <dataGirl(a)discussions.microsoft.com> wrote in message
news:747727D3-CA46-49BE-ACED-F7D5418FE7BF(a)microsoft.com...
> I'm trying to duplicate an Excel date/time format in Reporting Services
> 2005.
> On the Excel sheet, the difference between two dates is displayed
> formatted
> as "150:58:12" and then as "6.29". (12/1/2009 9:41:49 AM - 11/25/2009
> 2:43:37 AM). Is there a way to format the date/time in Reporting Services
> to
> do this?


From: dataGirl on 6 Apr 2010 17:03
When I try it, as soon as the minutes go beyond 60 it starts using the hour.
(EX. 2.03:22:55) I need it to not use the hour, but to continue using
minutes (123:22:55). Any ideas?

"Uri Dimant" wrote:

> dataGirl
> You can format dates with .NET formatting yyyMMdd os something like that
>
>
> "dataGirl" <dataGirl(a)discussions.microsoft.com> wrote in message
> news:747727D3-CA46-49BE-ACED-F7D5418FE7BF(a)microsoft.com...
> > I'm trying to duplicate an Excel date/time format in Reporting Services
> > 2005.
> > On the Excel sheet, the difference between two dates is displayed
> > formatted
> > as "150:58:12" and then as "6.29". (12/1/2009 9:41:49 AM - 11/25/2009
> > 2:43:37 AM). Is there a way to format the date/time in Reporting Services
> > to
> > do this?
>
>
> .
>
From: Bruce L-C [MVP] on 7 Apr 2010 09:59
This is interesting. I had a report I wrote years ago that shows the time
difference two ways. hh:mm:ss and total seconds. And as you want the hours
can be > 24. I knew I had done it but forgotten what I did.

I wanted total seconds whereas you want minutes but the logic will be the
same. This snippet shows how you do bet seconds or minutes in T-SQL

declare @FROMDATE datetime, @TODATE datetime
set @FROMDATE = '12/1/2009 9:41:49 AM'
set @TODATE = '11/25/2009 2:43:37 AM'

select abs(datediff(ss,@FROMDATE,@TODATE))as seconds
,abs(datediff(ss,@FROMDATE,@TODATE))/60.0 as Minutes

The datediff would be part of your query, have it return both seconds and
minutes. Minutes for you to display, seconds to pass into the code behing
report function below.

I then have some code behind report that takes the seconds and gives back
the format of hours:minute:second where hours can be > 24.

Public Function ConvertSecondsToHourMinSec(ByVal intTotalSeconds) As String
Dim intHours As Integer
Dim intMinutes As Integer
Dim intSeconds As Integer

intHours = intTotalSeconds \ 3600
intMinutes = (intTotalSeconds - (intHours * 3600)) \ 60
intSeconds = (intTotalSeconds - (intHours * 3600) - (intMinutes *
60))
ConvertSecondsToHourMinSec = Format(intHours, "#,#00") & ":" &
Format(intMinutes, "00") & ":" & Format(intSeconds, "00")

End Function

Then add a column to your table. Set the field to expression and put in
this:
=code.ConvertSecondsToHourMinSec( Fields!seconds.Value)

Now, there might be an easier way but at the time I did this (3 or 4 years
ago) this is how I solved the problem. I was having to duplicate an existing
report from a different system and needed to show it in exactly this way.


--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

"dataGirl" <dataGirl(a)discussions.microsoft.com> wrote in message
news:1EECC40C-10ED-4B65-BE4A-7925D77B992A(a)microsoft.com...
> When I try it, as soon as the minutes go beyond 60 it starts using the
> hour.
> (EX. 2.03:22:55) I need it to not use the hour, but to continue using
> minutes (123:22:55). Any ideas?
>
> "Uri Dimant" wrote:
>
>> dataGirl
>> You can format dates with .NET formatting yyyMMdd os something like that
>>
>>
>> "dataGirl" <dataGirl(a)discussions.microsoft.com> wrote in message
>> news:747727D3-CA46-49BE-ACED-F7D5418FE7BF(a)microsoft.com...
>> > I'm trying to duplicate an Excel date/time format in Reporting Services
>> > 2005.
>> > On the Excel sheet, the difference between two dates is displayed
>> > formatted
>> > as "150:58:12" and then as "6.29". (12/1/2009 9:41:49 AM - 11/25/2009
>> > 2:43:37 AM). Is there a way to format the date/time in Reporting
>> > Services
>> > to
>> > do this?
>>
>>
>> .
>>
 | 
Pages: 1
Prev: IIS hangs when accessing SQL Reports
Next: Custom Assembly error in SSRS 2008 Options