SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Script to find 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 example

Startdate =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

Vivek's SQL Notes

Vivek johari is currently a Analyst and have more that 5.5 yeras of experience in database. He has Master degree in Computer and also he is Microsoft certified Sql DBA (MCTS)& Microsoft certified SQl BI professional(MCTS). He is also Oracle certified profession(OCP)DBA in ORACLE 10g and ORACLE 9i.He has the experience of working in PL/SQL, T-SQL and SSIS/SSRS. His work basically involved designing and optimization of the Database.He has also published many database articles on his blog Technologies with Vivek Johari.

Comments

Leave a comment on the original post [feedproxy.google.com, opens in a new window]

Loading comments...