How to Format Dates in SSRS Using Custom Expressions

 In SQL Server Reporting Services (SSRS), customizing the date format is essential for improving the readability of reports. SSRS provides the Format() function, which allows for flexible formatting of date fields to meet different reporting requirements.

Example: Formatting Date to "October 26, 2024"

To display a date in the format October 26, 2024, use the following expression:

=Format(Fields!ICC_dc_datesupervisionorderedValue.Value, "MMMM dd, yyyy")

Key Parts of the Expression:

• Fields!ICC_dc_datesupervisionorderedValue.Value: This refers to the date field in your dataset.
• "MMMM dd, yyyy": This format displays:
  • MMMM: Full month name (e.g., October).
  • dd: Two-digit day (e.g., 26).
  • yyyy: Four-digit year (e.g., 2024).

Other Common Date Formats

Below are some other common formats you may need in SSRS:

1. Short Date (e.g., 10/26/2024):

   
   =Format(Fields!dateValue.Value, "MM/dd/yyyy")
   

2. Month and Year (e.g., October 2024):

   =Format(Fields!dateValue.Value, "MMMM yyyy")
   

3. Day, Month, and Year with Day of the Week (e.g., Saturday, October 26, 2024):

   =Format(Fields!dateValue.Value, "dddd, MMMM dd, yyyy")
   

4. Full Date with Time (e.g., October 26, 2024 14:35:45):

   =Format(Fields!dateValue.Value, "MMMM dd, yyyy HH:mm:ss")
   

   • HH:mm:ss: Displays hours (24-hour format), minutes, and seconds.

5. Short Date with Time (e.g., 10/26/2024 2:35 PM):

   =Format(Fields!dateValue.Value, "MM/dd/yyyy h:mm tt")
   

   • h:mm tt: Displays hours in 12-hour format with AM/PM.

6. ISO Date Format (e.g., 2024-10-26):

   =Format(Fields!dateValue.Value, "yyyy-MM-dd")
   

   • Commonly used for data exports and international formats.

7. Abbreviated Month, Day, and Year (e.g., Oct 26, 2024):

   =Format(Fields!dateValue.Value, "MMM dd, yyyy")
   

8. Year Only (e.g., 2024):

   =Format(Fields!dateValue.Value, "yyyy")
   

9. Day, Month, Year (e.g., 26 October 2024):

   =Format(Fields!dateValue.Value, "dd MMMM yyyy")
   

10. Month/Day without Year (e.g., October 26):

=Format(Fields!dateValue.Value, "MMMM dd")

Applying the Expression in SSRS

To apply these custom date formats in SSRS, follow these steps:

1. Open your report and select the textbox containing the date field.
2. Right-click the textbox and choose Expression....
3. Enter the desired expression from the examples above.
4. Preview the report to confirm the date is displayed correctly.

Conclusion

By using the Format() function in SSRS, you can easily control how dates are displayed in your reports. This allows you to tailor date formats to meet specific requirements, ensuring clarity and professionalism in your reports.