Using the DatePart() Function in SSRS can be tricky, even at the best of times. I have pulled my hair out too often trying to remember what syntax should be used, so have resorted to writing down all my findings. This is so that you don’t have to go through the same agony & frustration as I did, as well as for my own benefit (my memory is shot…)
What it does: Returns an Integer containing the specified part of a given date
Syntax: DatePart(interval, date[,firstdayofweek[,firstweekofyear]])
There are two ways to use this function:
- Using the DateInterval collection
- Using a string expression
Using the DateInterval Collection
Let’s start with an example:
To return the current month: =DatePart(DateInterval.Month, Today())
Now, in the expression builder (in BIDS), it will look like there’s something the matter with what we have just written:
You can ignore this, the expression will work. An important point to note when using the DateInterval collection, is that you must use a date that is of type Datetime. If you use a string representation of a date (“2009/11/13”), then you’ll need to convert this to Datetime using CDate(“2009/11/13”) before the expression will work. Thus:
This will work: =DatePart(DateInterval.Month, CDate(“2009/11/13”))
This won’t: = DatePart(DateInterval.Month, “2009/11/13”)
When you use the DatePart function to identify the day of the week you can specify what the First Day Of The Week is (defaults to Sunday if this is not specified). You can do this by accessing the FirstDayOfWeek collection. Example – To Return the day of the week, specifying Monday as the first day of the week:
DatePart(DateInterval.Weekday, CDate(“2009/11/13”), FirstDayOfWeek.Monday) returns 5 (Friday)
All the possibilities for using the DatePart() function with the DateInterval Collection:
The following table shows all the possibilities for the DatePart Function using the DateInterval collection. All expressions use a Parameter called MyDate (type = Datetime), which has a value of “2009/11/13 12:34:23”
| Expression | Result |
| DatePart(DateInterval.Second, Parameters!MyDate.Value) | 23 |
| DatePart(DateInterval.Minute, Parameters!MyDate.Value) | 34 |
| DatePart(DateInterval.Hour, Parameters!MyDate.Value) | 12 |
| DatePart(DateInterval.Weekday, Parameters!MyDate.Value, FirstDayOfWeek.Monday) | 5 |
| DatePart(DateInterval.Day, Parameters!MyDate.Value) | 13 |
| DatePart(DateInterval.DayOfYear, Parameters!MyDate.Value) | 317 |
| DatePart(DateInterval.WeekOfYear, Parameters!MyDate.Value) | 46 |
| DatePart(DateInterval.Month, Parameters!MyDate.Value) | 11 |
| DatePart(DateInterval.Quarter, Parameters!MyDate.Value) | 4 |
| DatePart(DateInterval.Year, Parameters!MyDate.Value) | 2009 |
Using a String Expression
Again, let’s start with an example:
To return the current month: =DatePart(“m”, Today())
Using a String expression for your interval enables you to use a date that is of datatype String:
=DatePart(“m”, “2009/11/13”)
All the possibilities for using the DatePart() function with a String Expression:
As noted previously, using a string expression for the interval allows you to use a date value that is of the String datatype. You can of course use a Datetime value if you wish to.
This table shows all the possibilities for the DatePart function using a String Expression for the interval. All expressions use a Parameter called MyDate (type = String), which has a value of “2009/11/13 12:34:23”
| Date Part | Expression | Result |
| Second | DatePart(“s”, Parameters!MyDate.Value) | 23 |
| Minute | DatePart(“n”, Parameters!MyDate.Value) | 34 |
| Hours | DatePart(“h”, Parameters!MyDate.Value) | 12 |
| Day of Week | DatePart(“w”, Parameters!MyDate.Value, FirstDayOfWeek.Monday) | 5 |
| Day of Month | DatePart(“d”, Parameters!MyDate.Value) | 13 |
| Day of Year | DatePart(“y”, Parameters!MyDate.Value) | 317 |
| Week of Year | DatePart(“ww”, Parameters!MyDate.Value) | 46 |
| Month | DatePart(“m”, Parameters!MyDate.Value) | 11 |
| Quarter | DatePart(“q”, Parameters!MyDate.Value) | 4 |
| Year | DatePart(“yyyy”, Parameters!MyDate.Value) | 2009 |
Is there a preferred method?
Ul
timately, no. At the end of the day, all the results are the same. What is my preference? I use the DateInterval collection, mainly becuase I like that fact that you can’t get confused about which date part you are using (w/ww, y/yyyy, n….). It’s very clear to anyone who is reading the code that (for example) DateInterval.WeekOfYear refers to the week of the year and nothing else. It also forces you to use a Datetime Value, which safeguards against invalid dates (sort of).
I hope this clears things up a little!
Nice summary Luke – I’m with you on documenting these things even for personal use – my personal SQL hate – Collate syntax ;)
Hi Owen,
Thanks! With so many variations on expression syntax across the BI stack, it’s no wonder us mortals get confused. At least I now have a reference point (for reporting services anyway!).
Am loving geeknom btw.
- Luke
Thanks, man! great info
Thanks, very helpful. Why can’t MSDN library be this useful?
Thx, I’ve searched about a half hour for it!
Thank you so much–this is very helpful!!
Very Helpful! Thank you very much!
Glad to be of service. Thanks for the comment, it is great to know that these posts are helping others out there!
Luke
Hi,
Really great information. I’m stil learning SSRS. I create reports and i get stuck in formulas everytime and then I have to google it. Your site came across, am definetly going to save this site for future reference.
I am stuck in one forumla, maybe you can help me or direct me. I want two paramters where I can select Start Month and end month for members”s bdate.
this is what I have so far:
(DATEPART(m, bdate) = @StartMonth) AND (DATEPART(d, bdate) <= 31) OR
(DATEPART(m, bdate) = @EndMonth) AND (DATEPART(d, bdate) <= 31)
Because of this OR, it only gives me Two months (for example, January for startmonth, and Endmonth whatever i specify)
Im looking for between months also.
I will wait for your reply.
Thanks,
Zara
Hi Zara,
I’m assuming that the script excerpt that you have given me is part of your query. If so, then you have the answer in your question:
“I’m looking for between months also”
You WHERE clause should more like this (assuming bdate is a Datetime data type):
DATEPART(m, bdate) BETWEEN @StartMonth AND @EndMonth
This will give you a result if the month of the member’s bdate falls on or between the months of the parameters that you have supplied.
I have six rooms which have classes scheduled weekly. I would like to see what the classes are by week. Show the class name, time, and instructor. The classes run over a range of dates. I would like to test if the current week falls within the range. The range could be 12/15/2010 to 3/15/2011. I cannot seem to get the DatePart function “ww” to work. Probably because I don’t understand it. I would appreciate all suggestions.
Hi Carole,
Could you send through the expression that you are using?
i need to default a date parameter to this format
05-apr-2011(ie for today’s date)
can u please tell me how to do it
Have you tried =Format(Fields!DateField.value, “d-MMM-yyyy”)?
have a look at http://thavash.wordpress.com/2007/10/02/date-formatting-in-reporting-services-list-of-format-codes/ for more on Date formatting codes.
Thank you, Very Helpful!
I’m trying to return a specific label based on what month a report is run in. I’ve put the following in the expression box, however I’m getting an “Overload resolution failed because no accessible ‘DatePart’ can be called without a narrowing conversion” error
IIF(DatePart(DateInterval.Month, Today() between 1 and 3, “4th Q PMs by Area, Type”)
IIF (DatePart(DateInterval.Month, Today() between 4 and 6, “1st Q PMs by Area, Type”)
IIF (DatePart(DateInterval.Month, Today() between 7 and 9, “2nd Q PMs by Area, Type”)
IIF (DatePart(DateInterval.Month, Today() between 10 and 12, “3rd Q PMs by Area, Type”)
can you help direct me in correcting the expression in order to produce the label I need?
Thanks!
Hi Margaret,
Firstly, I noticed that your expression was missing a few brackets – after “Today()”, and your IIF() statements, like birds eggs, needed to be nested:
=IIF(DatePart(DateInterval.Month, Today(),0,0) < 4, “4th Q PMs by Area, Type”,
IIF (DatePart(DateInterval.Month, Today(),0,0) < 7, “1st Q PMs by Area, Type”,
IIF (DatePart(DateInterval.Month, Today(),0,0) < 10, “2nd Q PMs by Area, Type”, “3rd Q PMs by Area, Type”)))
Secondly, this scenario would be better suited to using a SWITCH() function:
=SWITCH( DatePart(DateInterval.Month, Today()) < 4, “4th Q PMs by Area, Type”, DatePart(DateInterval.Month, Today()) < 7, “1st Q PMs by Area, Type”, DatePart(DateInterval.Month, Today()) < 10, “2nd Q PMs by Area, Type”, DatePart(DateInterval.Month, Today()) < 13, “3rd Q PMs by Area, Type”)
The SWITCH() Function evaluates each of the expressions in sequence and uses the first one that evaluates to ‘TRUE’.
Happy coding!
I’m still getting an error about an expected “)” in the expression and although you didn’t code it that way, I’m using Today()) after the date interval statement as I need it to evaluate what month or quarter it is when the report runs so that it pulls back the relevant data for the corresponding quarter. This statement will also be used in the fields in the matrix to get the correct sums.
Hi Margaret,
Whoops. It seems I was in a bit of a rush yesterday. I have updated the previous comment with the correct expression.
I also noticed that I completely forgot to add the “Today()” bit to the SWITCH expression! Both expressions now work as expected.
Thanks Luke — I had it figured out by the time you got this posted and my report looks great!