SSRS / SQL Server - How to remove seconds from time formatting?

Question

The format of a time column in one of my tables on SQL server was changed upon upload from HH:mm to HH:mm:ss. First off, is there any way to prevent this conversion in the first place? Second, now that it is in that format, how can I have it display simply as HH:mm? I have tried the following methods to no avail :

Set custom textbox formatting as HH:mm
Set time formatting in textbox as short time ie. 1:30 PM
Set the textbox value to the expression =LEFT(Fields!Time.Value,5)
Set the textbox value to the expression =LEFT(Fields!Time.Value,Len(Fields!Time.Value)-3)

the first two formatting attempts still return HH:mm:ss and using the Left function returns #Error.

Any ideas why this is happening and how to fix this?

Thanks

possible duplicate of [Best approach to remove time part of datetime in SQL Server](http://stackoverflow.com/questions/1177449/best-approach-to-remove-time-part-of-datetime-in-sql-server) — Kermit, Jul 11 '13 at 17:40
No. That question is asking how to remove the time portion of a datetime field. My question is how to remove the seconds portion of a strictly time field. — SELECTCOUNTSTAR, Jul 11 '13 at 17:47
I don't understand the point of suppressing seconds in the first place. Are you also saying that your `time` column wasn't accepting seconds? — Kermit, Jul 11 '13 at 17:58
It's extraneous information on a report that is short on space. Times were all inputted as 13:30 or 5:45, there's no need to see them as 13:30:00 and 5:45:00. — SELECTCOUNTSTAR, Jul 11 '13 at 18:36
Are you seeing this issue in the report as rendered in the browser, or is it an export? — Kevin Dahl, Jul 11 '13 at 19:30
Both in SSRS preview mode and when exported. The time data itself that is stored on the table is being stored as HH:mm:ss. — SELECTCOUNTSTAR, Jul 11 '13 at 19:34
This may be of use http://stackoverflow.com/questions/3846378/displaying-time-in-reporting-services-2008 ... specifically the bit about casting to datetime, and then using the SSRS time formatting. — Kevin Dahl, Jul 11 '13 at 19:37

score 3 · Answer 1 · answered Jul 11 '13 at 19:15

There are two ways, depending on how your field is set up in the report. The first thing you'll want to do is load up the report in Report Builder.

First I want to ensure that you have the properties pane visible, so under the view tab group on the ribbon, ensure you have the Properties pane set to visible:

View tab group showing check boxes for Report Builder panes

Formatting a field/expression value in textbox

If the field is in a text box containing multiple expressions, like this, where [DateTimeField] is the actual field from your dataset:

Image of a text box with the text "Time:" and a field named DateTimeField

Then you will select the field item itself:

Selection of the field named DateTimeField

Then, on the properties pane, you should see a "Format" setting. Set the format to "hh:mm", as shown:

The report builder window showing the format property of the field DateTimeField

Formatting an entire textbox

If your field in the report builder looks like this, you can set a formatting on the entire textbox:

Image of a cell in the report containing only a single field or expression

Select the textbox or cell:

The cell selected

And in the properties pane you can set the Format property to "hh:mm" to get the desired formatting on the item:

Report builder window showing the properties pane and the Format property set

If all else fails

Convert the data to the data type you know Report Builder can format, such as a Date type, and then format it there. To do this, you'll need to right click the field in question and select "Expression":

Context menu of right clicking on the DateTimeField field and showing the Expression menu item

Then you'll need to set the expression to wrap it in a CDate conversion function and then in a Format function, like so:

Expression panel showing the DateTimeField expression being coerced to a Date type and then formatted.

Formatting other data in your report

For more information on the valid formats, see these four pages from Microsoft's MSDN Library:

I stated in my original question that I had already tried setting the format of the textbox to hh:mm. It still shows hh:mm:ss. — SELECTCOUNTSTAR, Jul 11 '13 at 19:20
Have you tried coercing the type to date and using the format function, as in my "If all else fails" step? — Aaron Friel, Jul 12 '13 at 06:47

score 1 · Accepted Answer · edited May 23 '17 at 12:03

Assuming you have an actual TIME datatype, SSRS does not appear to be able to properly apply formatting to that datatype. In some cases you will see no formatting, and in others (such as with SSDT BI) you will get an actual error along the lines of:

[rsInvalidFormatString] The Format value for the textrun ‘CurrentTime_AS_TIME.Paragraphs[0].TextRuns[0]’ is not valid. Input string was not in a correct format.

Options to get past this are to cast the TIME value to a datetime in the query, or set the field as an expression like:

=Today + Fields!CurrentTime_AS_TIME.Value

Both should then allow you to format the field properly with the HH:mm format.

Referenced from here: Displaying Time in Reporting Services 2008

SSRS / SQL Server - How to remove seconds from time formatting?

2 Answers2

Formatting a field/expression value in textbox

Formatting an entire textbox

If all else fails

Formatting other data in your report