If you're using a normal database and not an SSAS cube, then you can use a Calendar table. Here's the article on it: http://www.sqlservercentral.com/articles/T-SQL/70482/

Basically, the Calendar table has a series of dates, and a bunch of other derived columns (hey, they're static data you can filter on!) you just join from your table to the date key in the Calendar table and then away you go!