The topic for this month’s TSQL Tuesday, hosted by Allen White, is an invitation to share your tricks. Before I get into my trick to share, I wanted to mention that Allen is a pretty awesome speaker too. I got to see his session “Gather SQL Server Performance Data with Powershell” at the SQL Saturday in Columbus earlier this year. Allen is really excited about SQL Server and the cool things you can do with Powershell.

Now for my trick, I have a view that was created to help write more dynamic rolling sql queries and reports in my organization. I’m not sure who the original author of the script is, as it has been passed around and modified several times. I thought I would share it here in the hopes that it helps someone else someday. If you’re the original author or know who is, please let me know so I can give you due credit.

This view defines a bunch of different date parameters compared to the current date, including:

TODAY_BEGIN

TODAY_END

YESTERDAY_BEGIN

YESTERDAY_END

DAY_BEFORE_YESTERDAY_BEGIN

DAY_BEFORE_YESTERDAY_END

SUNDAY_WEEK_BEGIN

SUNDAY_WEEK_END

MONDAY_WEEK_BEGIN

MONDAY_WEEK_END

PREVIOUS_SUNDAY_WEEK_BEGIN

PREVIOUS_SUNDAY_WEEK_END

PREVIOUS_MONDAY_WEEK_BEGIN

PREVIOUS_MONDAY_WEEK_END

MONTH_BEGIN

MONTH_END

YESTERDAYS_MONTH_BEGIN

YESTERDAYS_MONTH_END

PREVIOUS_MONTH_BEGIN

PREVIOUS_MONTH_END

SECOND_PREVIOUS_MONTH_BEGIN

SECOND_PREVIOUS_MONTH_END

THIRD_PREVIOUS_MONTH_BEGIN

THIRD_PREVIOUS_MONTH_END

FOURTH_PREVIOUS_MONTH_BEGIN

FOURTH_PREVIOUS_MONTH_END

TWELTH_PREVIOUS_MONTH_BEGIN

TWELTH_PREVIOUS_MONTH_END

PREVIOUS_SIXTH_MONDAY_WEEK_BEGIN

PREVIOUS_SIXTH_MONDAY_WEEK_END

PREVIOUS_SIXTH_SUNDAY_WEEK_BEGIN

PREVIOUS_SIXTH_SUNDAY_WEEK_END

NEXT_MONTH_BEGIN

NEXT_MONTH_END

You can take a quick look at the result returned from this view. After creating the view (script included below), run:

SELECT *
FROM vw_date_ranges

With these columns defined, you can easily query a database table looking for rows based off of a date by cross joining this view and the adding the date columns to the where clause. For example, if you want to see all orders for “This Week”, you can run a query similar to: