вторник, 1 февраля 2011 г.

The DatePart() Function in SSRS


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”
ExpressionResult
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 PartExpressionResult
SecondDatePart(“s”, Parameters!MyDate.Value)23
MinuteDatePart(“n”, Parameters!MyDate.Value)34
HoursDatePart(“h”, Parameters!MyDate.Value)12
Day of WeekDatePart(“w”, Parameters!MyDate.Value, FirstDayOfWeek.Monday)5
Day of MonthDatePart(“d”, Parameters!MyDate.Value)13
Day of YearDatePart(“y”, Parameters!MyDate.Value)317
Week of YearDatePart(“ww”, Parameters!MyDate.Value)46
MonthDatePart(“m”, Parameters!MyDate.Value)11
QuarterDatePart(“q”, Parameters!MyDate.Value)4
YearDatePart(“yyyy”, Parameters!MyDate.Value)2009

Is there a preferred method?
Ultimately, 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!


Read more: http://www.lukehayler.com/2009/11/the-datepart-function-in-ssrs/#ixzz1CkTmT02H

Комментариев нет: