How to display time value in a given row format in SSRS report?

Question

I want to show the length of time in the SSRS report. My DB field is the time in SQL. It converts Timespan to SSRS.

format: 1:00 PM - 3:50 PM

How can i do this?

Fields!StartTime.Value.ToString() + " PM - " + Fields!EndTime.Value.ToString() + " PM" is not working.. 

Answer 1

Here is one possible way to achieve this with Custom code in SSRS. The following example does not address the details of SSRS reporting, but should provide an idea of ​​how time can be formatted in SSRS.

Step by step:

• Create a table called dbo.Timespans using the script provided in SQL Scripts . Fill it with some data, as shown in screenshot # 1 .

• Create an SSRS report and use the dbo.Timespans table as a data source. See Screenshot # 2 .

• Click on Report and select Report Properties . Select the Code tab on the left.

• Paste the code specified in the SSRS Custom code section into the Custom Code text box. Click OK. This code takes the value timeSpan and format . Then it will format the time data and return as a string. See screenshot # 3 .

• Right-click on the time column and select Expression... Insert expression =Code.FormatTimeSpan(Fields!StartTime.Value, "hh:mm tt") + " - " + Code.FormatTimeSpan(Fields!EndTime.Value, "hh:mm tt") in the text field Set expression for: Value . See Screenshots # 4 and # 5 .

• Screenshot # 6 shows the progress of the report.

Hope this 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 No. 2:

Screenshot 3:

Screenshot 4:

Screenshot No. 5:

Screenshot No. 6: