Get month numbers and year between two dates

DECLARE
@start DATE = ‘20120101’
, @end DATE = ‘20131231’

;WITH cte AS
(
SELECT dt = DATEADD(DAY, -(DAY(@start) – 1), @start)

UNION ALL

SELECT DATEADD(MONTH, 1, dt)
FROM cte
WHERE dt < DATEADD(DAY, -(DAY(@end) – 1), @end)
)
SELECT DATEPART(m, dt) [Month] ,DATEPART(yyyy, dt) [YEAR]
FROM cte

— To get double digit month numbers use below query in the select statement.
SELECT  RIGHT(‘0’ + RTRIM(DATEPART(MM, dt)) FROM cte

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s