Calculate SQL From and To Date Month wise

June 9, 2023

declare @FromDate date = '2023-06-01'             
declare @ToDate date = '2023-08-05' 
 
 declare @MonthlyWiseTable as table
 (
    RowNo int,
    FromDate datetime,
    ToDate datetime,
    YearMonth varchar(100)
 )
  

  ;with dates_CTE (RowNo,FromDt,ToDt,YearMonth) as 
(
    select 
    1 as RowNo,
    @FromDate,
    EOMONTH(@FromDate),
    DATENAME(MONTH,@FromDate) +'-'+ CAST(YEAR(@FromDate) AS varchar(50))  
    Union ALL
    select  
    RowNo + 1, 
    DATEADD(DAY, 1, EOMONTH(FromDt)),    
    EOMONTH(DATEADD(MONTH, 1, FromDt)), 
    DATENAME(MONTH,EOMONTH(DATEADD(MONTH, 1, FromDt))) +'-'+ CAST(YEAR(EOMONTH(DATEADD(MONTH, 1, FromDt))) AS varchar(50))   
    from dates_CTE
    where FromDt <= @ToDate 
)
 
insert into @MonthlyWiseTable
select  *
from dates_CTE;
 
delete from @MonthlyWiseTable where RowNo = (select MAX(RowNo) from @MonthlyWiseTable)

 update R set R.ToDate = @ToDate from @MonthlyWiseTable R 
 where RowNo = (select MAX(RowNo) from @MonthlyWiseTable)

 select * from @MonthlyWiseTable

I want to calculate from Date Range of Month in given period it is working fine but it is not compact and proper solution , does any one knows how to calculate date range in compact way

>Solution :

A well indexed Calendar Table would be the simpliest way. Once you have a calendar table created you can easily query against that, returning only 1st day of each month and getting the last day with EOMONTH.

I use the calendar table in my Sandbox environment here, so the names of the objects and columns in your environment will likely differ, but the query is litterally as simple as follows:

DECLARE @FromDate date = '20230601',
        @ToDate date = '20230805';

SELECT ROW_NUMBER() OVER (ORDER BY CalendarDate),
       CT.CalendarDate AS FromDate,
       EOMONTH(CT.CalendarDate) AS ToDate,
       CONCAT(CT.CalenderMonthName,'-',CT.CalenderYear)
FROM dbo.CalendarTable CT
WHERE CT.CalendarDate >= @FromDate
  AND CT.CalendarDate <= @ToDate
  AND CT.CalenderDay = 1;