Last week I received my friend's email, After discussion in the MSN, send to me mail,
What About Date Time Experssion - SSRS
In our application year is considered to be divided into 4 quarters. Jan-March, April-Jun, Jul-Sep, Oct-Dec.
If I have selected my time range to be ‘Last Quarter’ and Current date is 2010/07/26. I should be able to get Start Date and End Date as 2010/04/01 and 2010/06/30 respectively. I wrote the following expressions to achieve this.
Start Date: =cdate(year(dateadd("q",-1,today())) & "/" & month(dateadd("q",-1,today())) & "/1"),
End Date: =dateadd("d", -1, cdate(year(dateadd("q",-0,today())) & "/" & month(dateadd("q",-0,today())) & "/1"))
If my current date is July it displays the start and end dates properly. However if my system date is in August it shows 2010/05/01 and 2010/07/31. This is because in RS the quarter stands for 3 months and it takes last 3 months dates.
How do I customize the date expression to consider the quarter separation as per our application requirement.
This is a SSRS function of the problem, so you can Design your Expression,Reference this.
Start Date: =Today().AddDays(1 - Today().Day).AddMonths(-(((Today().Month - 1) Mod 3) + 3))
End Date: =Today().AddMonths(-(((Today().Month - 1) Mod 3) + 3)).AddDays(-Today().Day).AddMonths(3)
I test it, is ok for SSRS.
p.s. My Test environment
1、Windows Server 2008 R2
2、Sql Server 2008 R2