SQL Server: Divide Date Range into Months

I wrote a script the other day that is utterly useless to me, but it might be helpful for someone else, so I thought I’d write a quick post about it.

Suppose you have some date range, let’s say “1/20/2013 – 4/29/2014”. Now, suppose you want this range to be broken up into one month sections, with a start and end date for each month. Suppose, for some reason, you want to use a recursive common table expression (CTE). You can just do this:

This would produce the following results:

months-cte-results

I think that this is actually pretty cool, but I can’t really come up with a reason to use something like this, outside of maybe a financial application. Anyway, I hope someone gets some use out of it.

Leave a Reply

Your email address will not be published. Required fields are marked *