Tuesday, November 9, 2010

SSRS Date Expressions

I've been doing a few CRM reports that used different date ranges as parameters. I was able to put the start and end dates of these ranges in SSRS parameters using the following expressions:

Start of Current Month:
=DateSerial(Year(Today), Month(Today), 1)
End of Current Month:
=DateAdd(DateInterval.Second, -1, DateSerial(Year(Today), Month(Today) + 1, 1))
Start Of Previous Month:
=DateSerial(Year(Today), Month(Today) - 1, 1)
End Of Previous Month:
=DateAdd(DateInterval.Second, -1, DateSerial(Year(Today), Month(Today), 1))
Start of Current Quarter:
=DateAdd(DateInterval.Quarter, DateDiff(DateInterval.Quarter, DateSerial(1900,1,1), Today), DateSerial(1900,1,1))
End of Current Quarter:
=DateAdd(DateInterval.Second, -1, DateAdd(DateInterval.Quarter, DateDiff(DateInterval.Quarter, DateSerial(1900,1,1) + 1, Today), DateSerial(1900,1,1)))
Start of Previous Quarter:
=DateAdd(DateInterval.Quarter, DateDiff(DateInterval.Quarter, DateSerial(1900,1,1), Today) - 1, DateSerial(1900,1,1))
End of Previous Quarter:
=DateAdd(DateInterval.Second, -1, DateAdd(DateInterval.Quarter, DateDiff(DateInterval.Quarter, DateSerial(1900,1,1), Today), DateSerial(1900,1,1)))
Start of Current Year:
=DateSerial(Year(Today), 1, 1)
End of Current Year:
=DateSerial(Year(Today), 12, 1)
Start of Previous Year:
=DateSerial(Year(Today) - 1, 1, 1)
End of Previous Year:
=DateSerial(Year(Today) - 1, 12, 1)

No comments:

Post a Comment