Script to find complete months between two given dates

Complete months between two given dates

Below script will help in finding the complete months within the 2 given dates. In the below script we takes below dates as exampleStartdate =2015-07-05
Enddate =2016-01-06

/**********************Script Start**************************/

IF (object_id(‘tempdb..#month’) is not null)
DROP TABLE #month

CREATE TABLE #month (id int identity(1,1),MonthStartDate DATETIME,MonthEndDate DATETIME)

DECLARE @startdate AS DATETIME
DECLARE @enddate AS DATETIME

DECLARE @monthstartdate AS DATETIME
DECLARE @monthenddate AS DATETIME

SET @startdate=’2015-07-05′
SET @enddate=’2016-01-06′

SELECT @monthstartdate= dateadd(mm,1,dateadd(DD,-day(@startdate)+1,@startdate))

SELECT @monthenddate= dateadd(dd,-1,dateadd(mm,1,@monthstartdate))

IF @enddate<@monthenddate
BEGIN

INSERT INTO #month
SELECT null,null
END
ELSE IF @startdate=@monthstartdate and @enddate=@monthenddate
BEGIN

INSERT INTO #month
SELECT @monthstartdate,@monthenddate
END
ELSE IF (@enddate>@monthenddate)
BEGIN

IF @startdate=@monthstartdate
BEGIN
INSERT INTO #month
SELECT @monthstartdate,@monthenddate
END

WHILE (@enddate>=@monthenddate)

BEGIN

INSERT INTO #month
SELECT @monthstartdate,@monthenddate

SELECT @monthstartdate=dateadd(MM,1,@monthstartdate)
SELECT @monthenddate=dateadd(day,-1,dateadd(MM,1,@monthstartdate))
END

END

SELECT cast(MonthStartDate as DATE) as MonthStart,cast(MonthEndDate as DATE) as MonthEnd FROM #month

/**********************Script End**************************/

This script will gives the following result:-

MonthStart MonthEnd
2015-08-01        2015-08-31
2015-09-01        2015-09-30
2015-10-01        2015-10-31
2015-11-01        2015-11-30
2015-12-01        2015-12-31

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in Interview Questions, SQL Basic, SQL Server and tagged , , . Bookmark the permalink.

Leave a Reply