SSRS / MDX Date Queries

Category: SQL Server Reporting Services
Hits: 14772
So I find myself using date ranges endlessly as I've been working in SSRS. The below is derived from a collection of various sources across the web as well as some of my own. These are what worked in my environment: WinXP, BIDS (vs2008), TFS (vs2010), SSRS 2008 R2. The following examples assume today's date is Wednesday 03 August 2011 @ 11:46:

This Week:
copyraw 
-- Start Date (US format - mm/dd/yyyy)
=DateAdd("d", -(WeekDay(Today(),2))+1, Today()) // yields: 8/1/2011

-- End Date (US format - mm/dd/yyyy)
=DateAdd("d", -1, DateAdd("d", 7-(WeekDay(Today(),2))+1, Today())) // yields: 8/7/2011

-------------------------------------------------------------------------

-- Start Date (european format - dd/mm/yyyy)
=Format(DateAdd("d", -(WeekDay(Today(),2))+1, Today()), "dd/MM/yyyy") //yields 01/08/2011

-- End Date (european format - dd/mm/yyyy)
=Format(DateAdd("d", -1, DateAdd("d", 7-(WeekDay(Today(),2))+1, Today())), "dd/MM/yyyy") // yields: 07/08/2011
  1.  -- Start Date (US format - mm/dd/yyyy) 
  2.  =DateAdd("d", -(WeekDay(Today(),2))+1, Today()) // yields: 8/1/2011 
  3.   
  4.  -- End Date (US format - mm/dd/yyyy) 
  5.  =DateAdd("d", -1, DateAdd("d", 7-(WeekDay(Today(),2))+1, Today())) // yields: 8/7/2011 
  6.   
  7.  ------------------------------------------------------------------------- 
  8.   
  9.  -- Start Date (european format - dd/mm/yyyy) 
  10.  =Format(DateAdd("d", -(WeekDay(Today(),2))+1, Today()), "dd/MM/yyyy") //yields 01/08/2011 
  11.   
  12.  -- End Date (european format - dd/mm/yyyy) 
  13.  =Format(DateAdd("d", -1, DateAdd("d", 7-(WeekDay(Today(),2))+1, Today())), "dd/MM/yyyy") // yields: 07/08/2011 

This Month:
copyraw 
-- Start Date (US format - mm/dd/yyyy)
=DateAdd("d",1-DatePart("d",Today()),Today()) // yields: 8/1/2011

-- End Date (US format - mm/dd/yyyy)
=DateAdd("d",-1, CDate(cstr(month(today.now())+1) + "/1/" + cstr(year(today.now()))) ) // yields: 8/31/2011

-------------------------------------------------------------------------

-- Start Date (european format - dd/mm/yyyy)
=CDate("01/" + cstr(month(today.now())) + "/" + cstr(year(today.now()))) //yields 1/8/2011

-- End Date (european format - dd/mm/yyyy)
=Format(DateAdd("d",-1, CDate(cstr(month(today.now())+1) + "/1/" + cstr(year(today.now()))) ), "dd/MM/yyyy") // yields: 31/08/2011
  1.  -- Start Date (US format - mm/dd/yyyy) 
  2.  =DateAdd("d",1-DatePart("d",Today()),Today()) // yields: 8/1/2011 
  3.   
  4.  -- End Date (US format - mm/dd/yyyy) 
  5.  =DateAdd("d",-1, CDate(cstr(month(today.now())+1) + "/1/" + cstr(year(today.now()))) ) // yields: 8/31/2011 
  6.   
  7.  ------------------------------------------------------------------------- 
  8.   
  9.  -- Start Date (european format - dd/mm/yyyy) 
  10.  =CDate("01/" + cstr(month(today.now())) + "/" + cstr(year(today.now()))) //yields 1/8/2011 
  11.   
  12.  -- End Date (european format - dd/mm/yyyy) 
  13.  =Format(DateAdd("d",-1, CDate(cstr(month(today.now())+1) + "/1/" + cstr(year(today.now()))) ), "dd/MM/yyyy") // yields: 31/08/2011 

This Year:
copyraw 
-- Start Date (US format - mm/dd/yyyy)
=DateSerial(YEAR(Today()),1,1) // yields: 1/1/2011

-- End Date (US format - mm/dd/yyyy)
=DateAdd("d", -1, DateSerial(YEAR(Today())+1,1,1)) // yields: 12/31/2011

-------------------------------------------------------------------------

-- Start Date (european format - dd/mm/yyyy)
=Format(DateSerial(YEAR(Today()),1,1), "dd/MM/yyyy") //yields: 01/01/2011

-- End Date (european format - dd/mm/yyyy)
=Format(DateAdd("d", -1, DateSerial(YEAR(Today())+1,1,1)), "dd/MM/yyyy") // yields: 
31/12/2011
  1.  -- Start Date (US format - mm/dd/yyyy) 
  2.  =DateSerial(YEAR(Today()),1,1) // yields: 1/1/2011 
  3.   
  4.  -- End Date (US format - mm/dd/yyyy) 
  5.  =DateAdd("d", -1, DateSerial(YEAR(Today())+1,1,1)) // yields: 12/31/2011 
  6.   
  7.  ------------------------------------------------------------------------- 
  8.   
  9.  -- Start Date (european format - dd/mm/yyyy) 
  10.  =Format(DateSerial(YEAR(Today()),1,1), "dd/MM/yyyy") //yields: 01/01/2011 
  11.   
  12.  -- End Date (european format - dd/mm/yyyy) 
  13.  =Format(DateAdd("d", -1, DateSerial(YEAR(Today())+1,1,1)), "dd/MM/yyyy") // yields: 
  14.  31/12/2011 

I'll add to this if I use others. Apologies for the European formats but this is what I work with so I like to just copy and paste from this article.
Category: SQL Server Reporting Services :: Article: 373

Add comment

Your rating:

Submit

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Latest Articles

Accreditation

Badge - Zoho Creator Certified Developer Associate
Badge - Zoho Deluge Certified Developer
Badge - Certified Zoho CRM Developer

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF

Joes Word Cloud

Please publish modules in offcanvas position.