CODEWITHSUNDEEP
datetimereporting-services
sacredfaith
sacredfaith

Reputation: 870

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

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?

Upvotes: 9

Views: 75069

Answers (4)

hurleystylee
hurleystylee

Reputation: 612

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)

Upvotes: 8

Nathan Cabisada
Nathan Cabisada

Reputation: 51

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

Try to input this:

dd-MMM-yy hh:mm tt

Upvotes: 5

sacredfaith
sacredfaith

Reputation: 870

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")

Upvotes: 11

Gil Peretz
Gil Peretz

Reputation: 2429

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

right click on the textbox > properties :

use the following: enter image description here

Upvotes: 11

Related Questions