CODEWITHSUNDEEP
reporting-servicesexpressionrdl
migmig
migmig

Reputation: 141

SSRS - Remove seconds from Time field

How do I remove seconds from Time field? I tried:

=Rset(Fields!hr_saida.Value,5) = return #Error
=TimeValue(Fields!hr_saida.Value) = return #Error
=FormatDateTime(CDate(Fields!hr_saida.Value),"hh:mm") = return #Error

I also tried set my text box property to Number > 13:30, but still shows "hh:mm:ss" How do I remove? Thank you.

Upvotes: 0

Views: 4630

Answers (3)

JSto234
JSto234

Reputation: 1

I had trouble returning what I wanted without error. I was already casting a datetime field to time so I just grabbed the left 5 characters (in SQL query) to trim off seconds. Not elegant, but got the job done.

So EndTime in my table is a datetime datatype with values like 2020-03-30 09:34:00.000. All I needed was short time without seconds so:

left(cast(EndTime as time),5) [End]

This changed report column outputs like 09:34:00 to 09:34 which is all I needed.

Upvotes: 0

Ian Preston
Ian Preston

Reputation: 39566

Another option in addition to the one from @Yuriy is:

=Format(Fields!hr_saida.Value, "HH:mm")

for 24 Hour time

or:

=Format(Fields!hr_saida.Value, "hh:mm")

for AM/PM.

If your field isn't DateTime you may need to use CDate() as well.

Edit after comment

Try:

=Left(Fields!hr_saida.Value.ToString(), 5)

RSet also works with ToString.

That works for 24 hour time - you could use something like:

=CDate(Fields!hr_saida.Value.ToString()).ToString("hh:mm")

for AM/PM.

In my testing I was getting similar errors like:

Unable to cast object of type 'System.TimeSpan' to type 'System.IConvertible'.

and

Conversion from type 'TimeSpan' to type 'Date' is not valid.

I guess the conclusion here is that SSRS really dislikes the SQL Server time datatype.

Upvotes: 4

suff trek
suff trek

Reputation: 39767

Use 4 option for FormatDateTime function.

=FormatDateTime(Fields!hr_saida.Value, 4)

Should do the trick.

Upvotes: 1

Related Questions