Reputation: 424
I want to Display time duration in SSRS report My DB field is time in SQL. It converts Timespan in SSRS.
format is : 1:00 PM - 3:50 PM
How can i do this ?
Fields!StartTime.Value.ToString() + " PM - " +
Fields!EndTime.Value.ToString() + " PM" is not working..
Upvotes: 6
Views: 7129
Reputation:
Here is one possible way of achieving this with the help of Custom code
in SSRS. Following example doesn't go into the details of creating SSRS reports but should give an idea of how the time formatting can be achieved within SSRS.
Step-by-step process:
Create a table named dbo.Timespans
using the script provided under SQL Scripts. Populate it with some data as shown in screenshot #1.
Create an SSRS report and use the table dbo.Timespans
as the data source. Refer screenshot #2.
Click on the Report
menu and select Report Properties
. Select the Code
tab on the left section.
Paste the code given under SSRS Custom code section in the Custom code textbox. Click OK. This code takes a timeSpan
value and format
string. It will then format the time data and return as a string. Refer screenshot #3.
Right-click on the time column and select Expression...
Paste the expression =Code.FormatTimeSpan(Fields!StartTime.Value, "hh:mm tt") + " - " +
Code.FormatTimeSpan(Fields!EndTime.Value, "hh:mm tt")
in the Set expression for: Value
textbox. Refer screenshots #4 and #5.
Screenshot #6 shows execution of the report.
Hope that helps.
SQL Scripts:
CREATE TABLE [dbo].[Timespans](
[Id] [int] IDENTITY(1,1) NOT NULL,
[StartTime] [time](7) NULL,
[EndTime] [time](7) NULL,
CONSTRAINT [PK_Timespans] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO
SSRS Custom Code:
public function FormatTimeSpan(timeSpanValue as TimeSpan, format as string) as string
Dim dateValue as DateTime
dateValue = new DateTime(timeSpanValue.Ticks)
return dateValue.ToString(format)
end function
Screenshot #1:
Screenshot #2:
Screenshot #3:
Screenshot #4:
Screenshot #5:
Screenshot #6:
Upvotes: 16