Last week I wrote a blog post about SQL SERVER – List the Name of the Months Between Date Ranges. It was written in the response to the question – How to list the name of the months between two date ranges? In the original blog post, I made a small error, hence I corrected that in the follow up blog post. Thanks to Sanjay Monpara for helping me correct the error. There are some amazing comments received in the original blog posts.
Here is the alternate solution proposed by Miladin Joksic and Peter Lalovsky. Miladin Joksic wrote the original script and Peter Lalovsky further improved it. It also retrieves the name of the months between date ranges as well have provided additional details like year as well month in numerics.
-- Count the months of @DateStart and @DateEnd
DECLARE @DateStart DATETIME = '2014-07-17' -- 2014 July
DECLARE @DateEnd DATETIME = '2015-07-08'; -- 2015 July
WITH Dates AS
SELECT DATEADD(DAY, -(DAY(@DateStart) - 1), @DateStart) AS [Date]
SELECT DATEADD(MONTH, 1, [Date])
WHERE [Date] < DATEADD(DAY, -(DAY(@DateEnd) - 1), @DateEnd)
YEAR([Date]) AS [Year]
, MONTH([Date]) AS [MonthNumber]
, RIGHT('0' + CAST(MONTH([Date]) AS VARCHAR(2)), 2) AS [MonthNumberLeadingZero] -- Leading zero
, DATENAME(MM, [Date]) AS [MonthName]
Thanks for amazing contribution to the original problem.
Reference: Pinal Dave (http://blog.sqlauthority.com)