how to get current month name, start date and end date between two dates in sql server

Viewed 2794
create procedure dummy2
as
begin
declare @sDate datetime,
        @eDate datetime

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

;with cte as (


  select  convert(datetime,left(convert(varchar,@sdate,112),6) + '01') startDate ,
       month(@sdate) n
  union all
  select dateadd(month,n,convert(datetime,convert(varchar,year(@sdate)) + '0101')) startDate,
        (n+1) n 

  from cte
  where n < month(@sdate) + datediff(month,@sdate,@edate)  
)

select CONVERT(varchar(20), DATENAME(MONTH, startdate))as Months, startdate, dateadd(day,-1,dateadd(month,1,startdate)) enddate
from cte
end

Months  startdate   enddate
February    2013-02-01 00:00:00.000     2013-02-28 00:00:00.000
March   2013-03-01 00:00:00.000 2013-03-31 00:00:00.000

hi i need to get the list of start date and end date ,month name bewteen two dates, above is my query but the result is not correct..i need the result like below

note : the start date and end date is what i given ,in between that days i neee the list of months starting date and ending date, month name

month | startdate | enddate


feburary | 2013-02-25 | 2013-02-28 march | 2013-03-01 | 2013-03-25

3 Answers
Related