I was recently using SQL Server Report Builder, and needed to format a date and time in a specific way. I went to edit the related expression, and looked for built-in functions to help me work with dates under Common Functions > Date & Time ... where I found FormatDateTime:
This is the example shown for how to use it:
=FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortDate)
This is helpful, because the 2nd argument is obviously a DateFormat enumeration ... but what are the other members besides ShortDate? I searched (like you might have) and couldn't come up with anything. The only thing I can assume, is that the DateFormat enumeration used in SSRS expressions is the same as the DateFormat enumeration in Visual Basic, which is:
- GeneralDate
- LongDate
- ShortDate
- LongTime
- ShortTime
None of these "canned" formats matched the way I wanted to format the date, so I kept looking and noticed some MSDN samples that used the Format method instead of the FormatDateTime method. In the expression edittor, that function can be found under Common Functions > Text:
The Format method is very flexible, and allows you to use any of the standard string-based format patterns you are probably already familar with. Here are a few common examples:
=Format(Fields!myDateTime.Value, "M/d/yy") ... 6/15/09
=Format(Fields!myDateTime.Value, "M/d/yyyy h:mmtt") ... 6/15/2009 2:45PM
=Format(Fields!myDateTime.Value, "MM/dd/yy HH:mm") ... 06/15/09 14:45
=Format(Fields!myDateTime.Value, "MMM d, yyyy") ... Jun 15, 2009
=Format(Fields!myDateTime.Value, "Short Date") ... 6/15/2009
=Format(Fields!myDateTime.Value, "Long Date") ... Monday, June 15, 2009
Of course, the standard format strings like "Short Date" and "Long Date" may vary depending on the culture set on your system. The examples above reflect the "en-US" configuration.
Then, here is a slight variation of the 2nd sample. It is my personal favorite and how I usually format all dates that have time values related to them, because I think the lowercase AM/PM designator makes the value easier for someone to scan, and requires less conscience effort to process. If it is all uppercase, the AM/PM designator seems to blend in with the rest of the text and you have to pay a little closer attention to interpret the related value (even if it is just a tenth of a second).
=LCase(Format(Fields!myDateTime.Value, "M/d/yy h:mmtt")) ... 3/15/2009 2:45pm