How Do I Format Date And Time On SSRS Report?

In SQL Server Reporting Services (SSRS), formatting date and time values can be done using built-in formatting functions in expressions or by using properties in the report’s design. Here’s how you can format date and time values in SSRS reports:

1. Formatting Date and Time in a Text Box

If you want to format a date/time field in a text box, you can do this by setting the Format property of the text box or by using an expression.

Using Format Property (for DateTime field)

1. Select the text box where the date/time value is displayed.
2. In the Properties pane, find the Format property.
3. Set the Format property to a date/time format string. SSRS uses standard .NET date/time format strings.

For example:

• Short Date: d
• Long Date: D
• Short Time: t
• Long Time: T
• Custom: yyyy-MM-dd HH:mm:ss

Using an Expression

If you need more flexibility or a custom date/time format, you can use an expression. To format a field in SSRS using an expression, do the following:

1. Right-click on the text box displaying the date or time.
2. Select Expression.
3. Use the Format function inside the expression to format the date or time. For example:

   =Format(Fields!YourDateField.Value, "yyyy-MM-dd HH:mm:ss")
   

   This expression formats the YourDateField to display the date and time in the format YYYY-MM-DD HH:MM:SS.

Common Date Format Strings

• MM/dd/yyyy – Formats the date as month/day/year (e.g., 01/20/2025).
• dd/MM/yyyy – Formats the date as day/month/year (e.g., 20/01/2025).
• yyyy-MM-dd – Formats the date as year-month-day (e.g., 2025-01-20).
• HH:mm – Formats the time as hour:minute (e.g., 15:30).
• HH:mm:ss – Formats the time as hour:minute:second (e.g., 15:30:45).
• dddd, MMMM dd, yyyy – Formats as day, month day, year (e.g., Monday, January 20, 2025).

2. Formatting Date and Time for Grouping or Sorting

When you’re grouping or sorting by date fields in SSRS, you can use expressions in the Group By or Order By fields to format the date for display purposes without affecting the underlying data.

For example, to group by year, you can use an expression like:

=Year(Fields!YourDateField.Value)

You can also format a date in the grouping field by:

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

3. Using Date Functions in Expressions

SSRS also provides several built-in date functions that you can use to manipulate and format date values directly in the expressions.

• Year(): Extracts the year from a date.

  =Year(Fields!YourDateField.Value)
  

• Month(): Extracts the month from a date.

  =Month(Fields!YourDateField.Value)
  

• Day(): Extracts the day of the month from a date.

  =Day(Fields!YourDateField.Value)
  

• DateAdd(): Adds a specified time interval to a date.

  =DateAdd(DateInterval.Day, 7, Fields!YourDateField.Value)
  

• DateDiff(): Calculates the difference between two dates.

  =DateDiff(DateInterval.Day, Fields!StartDate.Value, Fields!EndDate.Value)
  

4. Formatting Time Zones

If you need to display the date in a specific time zone, you can use custom expressions or adjust your date values by adding or subtracting hours, but SSRS does not have a built-in time zone conversion function. You’ll need to manually account for the time zone differences in your expressions.

For example, to adjust the date by 5 hours (for example, converting UTC to Eastern Time):

=DateAdd(DateInterval.Hour, -5, Fields!YourDateField.Value)

• Basic Format: Use the Format function to format date/time fields like yyyy-MM-dd HH:mm:ss.
• Built-in Formatting: Use standard date formats like d, D, t, and T for quick formatting.
• Custom Format: Use custom format strings like yyyy-MM-dd or MM/dd/yyyy.
• Grouping or Sorting: Use expressions to extract specific parts of a date (e.g., Year(), Month()).
• Date Functions: Utilize functions like DateAdd(), DateDiff(), and Year() for manipulating and formatting date values.

These methods will help you effectively display and manipulate date and time values in SSRS reports.