SSRS / MDX Date Queries

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:
-- 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

This Month:
-- 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

This Year:
-- 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

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.

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

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 - Valid till 8 May 2022 3QnhmaBX7LQSRsC9hh6Je9rGQKEGNQNfPb
© 2021 Joel Lipman .com. All Rights Reserved.