Print

Set default parameter dates to start and end of month

Situation
The requirement in a lot of my reports in SSRS has always been a date range rather than just the one date. The user must be allowed to select all results between two dates instead of just specifying the one day. If the second parameter (ToDate) is the same as the first (FromDate), then the range is for that particular date.

Solution
Easy for some but anything that I spend 30 minutes googling and reading up on is asking for an article on my own site. Besides this is how I did it and what worked for me...

Set the default values for your from and to date respectively:
copyraw
-- From Date (european format - dd/mm/yyyy)
=CDate("01/" + cstr(month(today.now())) + "/" + cstr(year(today.now())))

-- To Date (european format - dd/mm/yyyy)
=dateadd("d",-1, CDate("01/" + cstr(month(today.now())+1) + "/" + cstr(year(today.now()))) )

-- From Date (american format - mm/dd/yyyy)
=CDate(cstr(month(today.now())) + "/1/" + cstr(year(today.now())))

-- To Date (american format - mm/dd/yyyy)
=dateadd("d",-1, CDate(cstr(month(today.now())+1) + "/1/" + cstr(year(today.now()))) )
  1.  -- From Date (european format - dd/mm/yyyy) 
  2.  =CDate("01/" + cstr(month(today.now())) + "/" + cstr(year(today.now()))) 
  3.   
  4.  -- To Date (european format - dd/mm/yyyy) 
  5.  =dateadd("d",-1, CDate("01/" + cstr(month(today.now())+1) + "/" + cstr(year(today.now()))) ) 
  6.   
  7.  -- From Date (american format - mm/dd/yyyy) 
  8.  =CDate(cstr(month(today.now())) + "/1/" + cstr(year(today.now()))) 
  9.   
  10.  -- To Date (american format - mm/dd/yyyy) 
  11.  =dateadd("d",-1, CDate(cstr(month(today.now())+1) + "/1/" + cstr(year(today.now()))) ) 
This idea was based on Bob Griffin's post on SQL Server Central.com forums where he subtracts 1 day from the first day of next month to get the last date/number of days in this month.
Category: SQL Server Reporting Services :: Article: 337