SQL Month Between Two Dates

Using below query you can get months between two dates. create temporary table @tbl_Date_Range and provide from date and to date to get months between two dates.for example from jan-2016 to dec-2016.

DECLARE @From_Date DATETIME,@To_Date DATETIME;
DECLARE @tbl_Date_Range TABLE(RowID int NOT NULL primary key identity(1,1), MONTH DATETIME);

DECLARE @Initial_Date DATETIME;

SET @From_Date =’2016-01-01′
SET @To_Date =’2016-12-31′

SELECT @Initial_Date = DATEADD(mm, DATEDIFF(mm, 0, @From_Date), 0)

INSERT INTO @tbl_Date_Range
SELECT  DATEADD(MONTH, x.number , @Initial_Date) AS MonthName
FROM    master.dbo.spt_values x
WHERE   x.type = ‘p’
AND     x.number <= DATEDIFF(MONTH, @Initial_Date, @To_Date)

SELECT RowID,REPLACE(RIGHT(CONVERT(VARCHAR(20), MONTH, 6), 6), ‘ ‘, ‘-‘) FROM @tbl_Date_Range

Now run this query and you will get output as below image that show months between jan-2016 and dec-2016.

sql_4

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