The SSRS DatePart function is a Date and Time function that will help you extract individual date parts (specified component) from the given date fields and return the integer value. For instance, extract years, months, hours, days, etc., from the order date for analysis purposes. The syntax of the SSRS DatePart function to return the required date or time part from the given date is as shown below.
=DatePart("m", Fields!HireDate.Value)
=DatePart(DateInterval.Day, Fields!OrderDate.Value, FirstDayOfWeek.System, FirstWeekOfYear.System )
To demonstrate the SSRS DatePart function, we use the Employee table below, which has 15 records. The image shows the records in the Table report.
SSRS DatePart function Example
For this, let me add a new column to the right side of the Hire Date column and name it DatePart. Next, right-click the textbox under Now and choose the Expression. To understand the report, I suggest you refer to the articles on charts, tables, grouping, and format tables in SSRS.
It opens the following expression window to extract the date parts. The below SSRS DatePart function uses the Year as the first argument, extracting and returning the year value from the HireDate column.
=DatePart("yyyy", Fields!HireDate.Value)
Similarly, let me add a new column to show the remaining arguments. The below code will set the first day of the week as Monday and the first week of the year as January 1 to extract the Quarter value. For more functions >> Click Here!
=DatePart("q", Fields!HireDate.Value, FirstDayOfWeek.Monday, FirstWeekOfYear.Jan1)
Please click the preview tab to see the report.
Let me add a new column to demonstrate the possible options of the first arguments. The SSRS DatePart function expression below extracts each part of the date field, such as year, Month, Day, Quarter, week, Weekday, Day of the Week, Hours, Minutes, and Seconds. To ensure you understand the first arguments, we have joined the names of the return values.
="Year = " & DatePart("yyyy", Fields!HireDate.Value) &
" Month = " & DatePart("m", Fields!HireDate.Value) &
" Day = " & DatePart("d",Fields!HireDate.Value) &
" Quarter = " & DatePart("q", Fields!HireDate.Value) &
" Week = " & DatePart("ww", Fields!HireDate.Value) &
" Weekday = " & DatePart("w", Fields!HireDate.Value) &
" Day of the Week = " & DatePart("y", Fields!HireDate.Value) &
" Hour = " & DatePart("h", Fields!HireDate.Value) &
" Minute = " & DatePart("n", Fields!HireDate.Value) &
" Second = " & DatePart("s", Fields!HireDate.Value)
If you don’t know the abbreviations like m, s, h, etc., typing DateInterval followed by a full stop will show the available ones. Please click the preview tab to see the report.
