How do I format time in SSRS to HH:MM AM/PM format?

Question

I was tasked with augmenting the following code to display the time without seconds, and with AM/PM:

=IIF(Fields!New_Date.Value <> "NO CHANGE", FormatDateTime(IIF(Fields!New_Date.Value = "NO CHANGE","1/1/12",Fields!New_Date.Value),DateFormat.ShortDate), "") &
IIF(Fields!New_Time.Value <> "NO CHANGE",FormatDateTime(IIF(Fields!New_Time.Value = "NO CHANGE","00:00",Fields!New_Time.Value),DateFormat.ShortTime), "")

In realizing that FormatDateTime was insufficient for what I was trying to do, I found the following did not work (just looking at the snippit that relates to the time fields), :

Format(IIF(Fields!New_Time.Value = "NO CHANGE","00:00",Fields!New_Time.Value),"HH:mm tt")  

Or this

Format(IIF(Fields!New_Time.Value = "NO CHANGE","00:00",Fields!New_Time.Value),"HH:MM tt")

I'm getting the format codes from here.

What am I doing wrong?

Answer 1

If your data is in simple textbox(or table cell) you have easer solution.

right click on the textbox > properties :

use the following:

Answer 2

So I found that because I was essentially trying to format a textbox, SSRS never really 'knew' that it was a time field. While I'm not 100% sure my reasoning is correct, it does explain why the following works:

Format(CDate(IIF(Fields!New_Time.Value = "NO CHANGE","00:00",Fields!New_Time.Value)),"hh:mm tt")

Answer 3

The other answers are correct, but the key difference to know with the format is:

 h:mm tt --> 12-hour clock (e.g. 5:30 PM)
hh:mm tt --> 12-hour clock with a leading zero, if necessary (e.g. 05:30 PM)
 H:mm    --> 24-hour clock (e.g. 1:30 for 1:30 AM)
HH:mm    --> 24-hour clock with a leading zero, if necessary (e.g. 01:30 for 1:30 AM)

Answer 4

From Text Box Properties -> Number -> Custom Category:

Try to input this:

dd-MMM-yy hh:mm tt