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] UNION ALL SELECT DATEADD(MONTH, 1, [Date]) FROM Dates WHERE [Date] &lt; DATEADD(DAY, -(DAY(@DateEnd) - 1), @DateEnd) ) SELECT 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] FROM Dates;
Thanks for amazing contribution to the original problem.
Reference: Pinal Dave (https://blog.sqlauthority.com)