Recently I was building a mini-database for personal use and was storing separate fields for Date and Time. It seemed like a good idea until I tried to display the time field in SSRS. I found that:
If the database schema specifies this:
And your field is displayed like this:
With a standard format like this:
You'll get horrible output like this:
The reason the standard formatting string doesn't work is that Time:
Defines a time of a day. The time is without time zone awareness and is based on a 24-hour clock.
Meanwhile SSRS translates it a .NET equivalent called TimeSpan which is a little different and can't be formatted in the same way:
A TimeSpan object represents a time interval (duration of time or elapsed time) that is measured as a positive or negative number of days, hours, minutes, seconds, and fractions of a second. The TimeSpan structure can also be used to represent the time of day, but only if the time is unrelated to a particular date.
The quickest way I found to display it, while still using the traditional "HH:mm" format string, is to use an expression like this: