CODEWITHSUNDEEP
sqlsql-server
Farhad-Taran
Farhad-Taran

Reputation: 6540

Get start and end dates of month in between two dates in SQL?

I have the following function which is supposed to return the start and end dates of the months in between two months, the problem how ever is that since this month is 28 days the function is calculating all the upcoming months on a 28 day basis thus returning the following wrong values.

StartDate   EndDate
-----------------------   
2013-02-01  2013-02-28 
2013-03-01  2013-03-28 
2013-04-01  2013-04-28 



declare @sDate datetime,
        @eDate datetime;

select  @sDate = '2013-02-25',
        @eDate = '2013-04-25';

;with months as
(
  select DATEADD(mm,DATEDIFF(mm,0,@sDate),0) StartDate, 
  DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@sDate)+1,0)) EndDate

  union all
  select dateadd(mm, 1, StartDate),
    dateadd(mm, 1, EndDate)
  from months
  where dateadd(mm, 1, StartDate)<=  @eDate
)

select * from months

how can I modify this to return the right dates?

Upvotes: 2

Views: 13650

Answers (5)

Six
Six

Reputation: 1

End of Month:

@pDate = EOMONTH(GETDATE())

Starting Date:

@pDate = DATEADD(DAY,-1 * (DAY(GETDATE())-1),GETDATE())

Upvotes: 0

Ayodele
Ayodele

Reputation: 11

You can use the code below

create FUNCTION [dbo].fn_getfirstandenddate (
      @StartDate  DATE,
      @EndDate DATE
)
RETURNS @Items TABLE (
      StartDate DATE ,EndDate DATE
)AS
BEGIN
insert into @ItemsSELECT case when @startdate > DATEADD(DAY,1,EOMONTH((DATEADD(MONTH, x.number, @StartDate)),-1)) then @startdate else DATEADD(DAY,1,EOMONTH((DATEADD(MONTH, x.number, @StartDate)),-1)) end startDay,case when @enddate < eomonth(DATEADD(MONTH, x.number, @StartDate)) then @enddate else eomonth(DATEADD(MONTH, x.number, @StartDate)) end endDate FROM    master.dbo.spt_values x WHERE   x.type = 'P'AND     x.number <= DATEDIFF(MONTH, @StartDate, @EndDate);

      RETURN

END

Upvotes: 1

Bhanu Sisodiya
Bhanu Sisodiya

Reputation: 11

Try This : It's a table function. Return a table of month start and end Date with partial Date Also.

    CREATE FUNCTION [dbo].[Fun_GetFirstAndLastDateOfeachMonth] (
          @StartDate  DATE,
          @EndDate DATE
    )

    RETURNS @Items TABLE (
          StartDate DATE ,EndDate DATE,MonthNumber INT,YearNumber INT
    )

    AS
    BEGIN
            ;WITH cte AS (
              SELECT CONVERT(DATE,LEFT(CONVERT(VARCHAR,@StartDate,112),6) + '01') startDate,
                     MONTH(@StartDate) n
              UNION ALL
              SELECT DATEADD(MONTH,N,CONVERT(DATE,CONVERT(VARCHAR,YEAR(@StartDate)) + '0101')) startDate,
                    (n+1) n
              FROM cte
              WHERE n < MONTH(@StartDate) + DATEDIFF(MONTH,@StartDate,@EndDate)
            )
            INSERT INTO @Items
            SELECT  CASE WHEN  MONTH(startdate) = MONTH(@StartDate) THEN @StartDate ELSE startdate END AS StartDate, 
            CASE WHEN  MONTH(startdate) = MONTH(@EndDate) THEN @EndDate ELSE DATEADD(DAY,-1,DATEADD(MONTH,1,startdate)) END AS  enddate,
            MONTH(startDate) AS MonthNumner,YEAR(startDate) AS YearNumber
            FROM cte

          RETURN

    END -- End Function

Execute This After Creating The Function 

SELECT * From [EDDSDBO].[Fun_GetFirstAndLastDateOfeachMonth] ('2019-02-25','2019-05-20')

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

If you can get the first day of the month, use dateadd twice to get the last day.

First, add 1 month, then subtract 1 day.

Upvotes: 1

Kaf
Kaf

Reputation: 33839

Try this;

declare @sDate datetime,
        @eDate datetime

select  @sDate = '2013-02-25',
        @eDate = '2013-04-25'

;with cte as (
  select convert(date,left(convert(varchar,@sdate,112),6) + '01') startDate,
         month(@sdate) n
  union all
  select dateadd(month,n,convert(date,convert(varchar,year(@sdate)) + '0101')) startDate,
        (n+1) n
  from cte
  where n < month(@sdate) + datediff(month,@sdate,@edate)
)
select startdate, dateadd(day,-1,dateadd(month,1,startdate)) enddate
from cte

FIDDLE DEMO

|  STARTDATE |    ENDDATE |
---------------------------
| 2013-02-01 | 2013-02-28 |
| 2013-03-01 | 2013-03-31 |
| 2013-04-01 | 2013-04-30 |

Upvotes: 8

Related Questions