In this article we will show you, How to Format Date and Time in SSRS Report. This is one of the Frequently asked question by the users. So, here we will show you the step by step approach to format Date and Time in SSRS Report using the text box properties, and the Expression with example.
To explain the available Date and Time Format options, We are going to use below shown Data Set. Please refer Embedded Data Source and Dataset articles to understand the steps involved in creating Embedded Data Source, and Dataset that we used for this report.
and the Custom SQL query that we used in the above Data set is:
SQL Query
-- Formatting Date and Time on SSRS Report
SELECT [FirstName] + ' ' + [LastName] AS FullName
,[Education]
,[Occupation]
,[YearlyIncome]
,[Sales]
,[HireDate]
FROM [Employee]
Data written by the above query is:
Format Date and Time in SSRS Report
To explain the list of Data and Time formatting options available in SSRS, We are going to use below shown report. Please SSRS Table Report article to understand the steps involved in creating Table Report, refer Format Table Report in SSRS article to understand the formatting, and Add headers and Footer to SSRS Report article for adding Page headers, and footers.
Let me show you the Report Preview. Here our task is to format the Hire Date column
We had two approaches to format the Date, and Time: one is using the Text Box Properties, and the other is using expression. Let me explain the First option here, and later we will show the second option.
Select the Text Box containing Date values. In this example, Select Hire Date Text box of the SSRS report and Right click on it will open the context menu. Please select the Text Box Properties.. option from it
Once we click on the Text Box Properties.. option, a new Text box Properties window will be opened. Please select the Number tab, and then date category as shown in below screenshot.
Let me change the default date time format to Monday, January 31, 2000 1:30 PM
As you can see from the below screenshot that our report is displaying the Formatted Date and Time in Hire Date column
Again, Let me change the date time format to Monday, January 31, 2000 1:30:00 PM
As you can see from the below screenshot that our report is displaying the Date and Time with AM and PM in Hire Date column
This time we will try the shortest date format 31 Jan 2000
Our report preview is displaying the Hire date in same format.
If you observe the Number tab in Text Box properties, we have another category called Time. This category is very useful to format time (exclusively). Let me select the format as 31-Jan-00 1:30 PM
Our report preview is displaying the Hire date in same format.
Format Date and Time in SSRS Report using Expression
SSRS supports Expressions, and it is one of the powerful concept available for the developers. If your desired format is not in the list of categories then you can use the expression to write a custom date and time.
Please select Hire Date Text Box of the SSRS report and Right click on it will open the context menu. Please select the Expression.. option from it
Once you select the Expression.. option, a new window called an Expression will be opened as we shown below. Under the category section -> Common Functions -> Date and Time we have many date and time functions. We can use any of the function, or combination of function to achieve the desired result.
For now we are formatting the Hire date to short date
As you can see the Hire date column is saying that it holds an Expression, rather than Column name Hire date.
As you can see that our report preview is displaying the same in Hire date column data.
Thank You for Visiting Our Blog
