months between two dates in sql server with starting and end date of each of them in sql server

Question

i want to get months between two dates with their starting and end dates.Suppose if i enter startdate as "2017-04-01" and enddate as "2017-07-31", i want list of months i.e April,May,June,July with their starting and end date respectively.Kindly suggest me how it can be achieved.

Answer 1

One method is a recursive CTE:

with cte as (
      select dateadd(day, 1 - day(@startdate), @startdate) as som,
             eomonth(@startdate) as eom
      union all
      select dateadd(month, 1, som), eomonth(dateadd(month, 1, som))
      from cte
      where dateadd(month, 1, som) < @enddate
     )
select *
from cte;

If you want the name of the month, then you can use datename(month, som).

Answer 2

Without recursion, using master.dbo.spt_values as a substitute for a numbers table:

declare @StartDate date = '20170401'
      , @EndDate   date = '20170731';
;with Months as (
  select top (datediff(month,@startdate,@enddate)+1) 
      [Month] = dateadd(month, row_number() over (order by number) -1, @StartDate)
    , MonthEnd = dateadd(day,-1,dateadd(month, row_number() over (order by number), @StartDate))
  from master.dbo.spt_values
  order by [Month]
)
select * from Months;

rextester demo: http://rextester.com/FXQJ4048

returns:

+------------+------------+
|   Month    |  MonthEnd  |
+------------+------------+
| 2017-04-01 | 2017-04-30 |
| 2017-05-01 | 2017-05-31 |
| 2017-06-01 | 2017-06-30 |
| 2017-07-01 | 2017-07-31 |
+------------+------------+

---

When generating a set or sequence in SQL Server, methods that avoid recursion and loops perform significantly better as the number of values increases.

Reference:

• Generate a set or sequence without loops - 1 - Aaron Bertrand
• Generate a set or sequence without loops - 2 - Aaron Bertrand
• Generate a set or sequence without loops - 3 - Aaron Bertrand

---

To get the start and end dates of each month within a given range, when the value of the @StartDate parameter is not the first day of the month:

The first option is to truncate the @StartDate parameter to the first of the month, the second option is to adjust the expressions in the common table expression to truncate the values there:

declare @StartDate date = '20170415'
      , @EndDate   date = '20170715';
/* Option 1: truncate @StartDate to the beginning of the month */
--set @StartDate = dateadd(month, datediff(month, 0, @StartDate), 0);
/* Option 2: Truncate @StartDate to month in the common table expression: */
;with Months as (
select top (datediff(month,@StartDate,@EndDate)+1) 
    [Month] = dateadd(month
               , datediff(month, 0, @StartDate) + row_number() over (order by number) -1
               , 0)
    , MonthEnd = dateadd(day,-1,dateadd(month
               , datediff(month, 0, @StartDate) + row_number() over (order by number) 
               ,0))
  from master.dbo.spt_values
  order by [Month]
)
select * from Months;

Answer 3

Here you go...

created the schema

   create table abc(
      date1 date
    )

//Inserting data into it
    insert into abc values(getdate()), 
    (DATEADD(Month, -1, getdate())),
    (DATEADD(Month, -2, getdate())),
    (DATEADD(Month, -3, getdate())), 
    (DATEADD(Month, -4, getdate()))

and finally the Select Query to fetch the data between Start date and end date:

select (datename(Month, date1)+' '+convert(varchar(2), date1, 103)) as [Date] from abc 
where convert(varchar(10), date1, 120) between '2017-05-02' and '2017-07-02'

Another approach to fetch the between two dates data:

select (datename(Month, date1)+' '+convert(varchar(2), date1, 103)) as [Date] from abc
where date1 >= (DATEADD(Month, -3, getdate())) AND date1 <=getdate();

And the returned result is:

this is the Fiddle where you can test this query -> SQL FIDDLE Simple and easy...good luck bro :)

Answer 4

Try this:

 DECLARE @Start DATE ='2017-04-01', 
        @End   DATE ='2017-07-31'


SELECT *, Datename(mm, date), 
       Dateadd(mm, Datediff(mm, 0, date), 0) AS FirstDateOfMonth, 
       Dateadd (dd, -1, Dateadd(mm, Datediff(mm, 0, date) + 1, 0)) as 
      LastDateOfMonth
FROM   dbo.TableName
WHERE  Cast(date AS DATE) BETWEEN @Start AND @End 

Answer 5

I created a stored procedure for that, may be you can convert that into user defined Function. Posting that code below,

create procedure ListMonths
@date1 date,@date2 date 
as
begin
create Table #tempTable
(mnth varchar(10))
while @date1<@date2
begin
insert into #tempTable
select DATENAME(month,@date1)
set @date1 = DATEADD(MONTH,1,@date1)
end
select * from #tempTable;
drop table #tempTable;
end

To execute the stored procedure:

exec ListMonths '2017-04-01','2018-01-31'

output

+------------+
|   mnth     |
+------------+
| April      |
| May        | 
| June       |
| July       |
| August     |
| September  |
| October    |
| November   |
| December   |
| January    |
+------------+

result

Answer 6

If this is not just a one-off report, then I would create a calendar table, and use that to "group by". This will also let you do many other date related calculations. You can find one at simple calendar or one here at Stackoverflow

Then your code could look like this:

SELECT top 10000 * FROM dbo.calendar DD
WHERE DD.TimeStampFrom>='2017-04-01' AND DD.TimeStampFrom <='2017-07-31'
AND DAY(DD.TimeStampFrom)=1