A few years ago I created an article around Reporting Services and dates. It could have been written more generically, because I reference this quite a bit to get common dates like "the beginning of this week", "midnight last night", etc, in my SQL queries. It's a fairly comprehensive list of relative dates that one might want to get in T-SQL for reporting, scheduling, etc.
It can get pretty complex, such as this function for getting the end of the current week
CREATE FUNCTION get_week_end (@date datetime)RETURNS datetime ASBEGIN return dateadd(yyyy, datepart(yyyy, dateadd(weekday,7-datepart(weekday, @date),@date))-1900, 0) + dateadd(ms, -3, dateadd(dy, datepart(dy, dateadd(weekday,7-datepart(weekday, @date),@date)),0) )END
If you don't find what you need, you can typically use the dateadd function to tweak one of these. Here is the complete list outlined in the article: