Months Between Two Dates in SQL Server with Starting and End Date of Each of Them in SQL Server

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

This is how to write the recursive CTE proposed by Caius Jard on the comments :

-- Create a Test Table
select '2019-07-05' as StartDateUtc, '2019-09-19' as EndDateUtc
into #test;

-- Recursive CTE that returns the months between StartDateUtc and EndDateUtc
with months as (
  -- initial values of the recursive CTE
  select datefromparts(year(StartDateUtc), month(StartDateUtc), 1) as StartPeriod,
         dateadd(month, 1, datefromparts(year(StartDateUtc), month(StartDateUtc), 1)) as EndPeriod,
         t.StartDateUtc, t.EndDateUtc
  from #test t
  union all
  -- we recursively add 1 month until we reach EndDateUtc
  select dateadd(month, 1, StartPeriod) as StartPeriod,
         dateadd(month, 1, EndPeriod) as EndPeriod,
         months.StartDateUtc, months.EndDateUtc
  from months 
  where dateadd(month, 1, StartPeriod) <= coalesce(EndDateUtc, StartDateUtc)  -- If EndDateUtc is null then we use StartDateUtc, so only the first month is returned
)
select * from months;

-- Drop Test Table
drop table #test;

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

You need a table of numbers, here i use the simplest one

select BusinessRefId 
  , cast(ys.y as varchar(4)) + '-' + cast(ys.y + 1 as varchar(4))  Period
  , GoalType, Amount
  , dateadd(year, nmbs.n, tbl.StartDateUtc) StartDateUtc
  , dateadd(year, nmbs.n + 1,tbl.StartDateUtc) EndDateUtc
  , Currency
from 
-- your real table here
(values (1, 'year', 'CommittedTransactionFee', 1800, cast('20160607' as date),  cast (null as date), 'USD')) 
tbl(BusinessRefId, Period, GoalType, Amount, StartDateUtc, EndDateUtc, Currency)
join 
-- table of numbers
(values (0),(1),(2) --,..
 ) nmbs(n)
on dateadd(year, nmbs.n + 1, tbl.StartDateUtc) <= getdate()
cross apply (select Year(tbl.StartDateUtc) + nmbs.n y ) ys

EDIT

See the fiddle
This is the version of my query with more conditions regarding which rows needed (note differencies in ON clause) and how to compute end date. It uses Soundappan's ddl and data (extended) which are allegebly satifactory close to the real ddl and data.
The main idea is the same, use a table of numbers. You may want to have instantiated table in your DB to use it in other similar queries.

How to split months between a start date and end date from a table in SQL

I think I have got the idea of what you are trying to do. This will do it all in one query.

First set up your test data (correcting your typo where enddate was < startdate)

create table xchg(startdate date, enddate date, currency varchar(3))
insert xchg values ('2016-01-18','2016-05-30','EUR')
,('2017-03-19','2017-05-31','BDT')

Then a recursive query picking out each anniversary between the two dates. Don't know where you are getting the exchange rate from, but you should be able to add it to the this.

;with splits as
(
    select *, startdate as split from xchg
    union all
    select startdate, enddate, currency, 
        dateadd(m,1,split) 
    from splits
    where dateadd(m,1,split) <= enddate
)
select * from splits order by currency, split

Result is:

startdate   enddate     currency    split
2017-03-19  2017-05-31  BDT         2017-03-19
2017-03-19  2017-05-31  BDT         2017-04-19
2017-03-19  2017-05-31  BDT         2017-05-19
2016-01-18  2016-05-30  EUR         2016-01-18
2016-01-18  2016-05-30  EUR         2016-02-18
2016-01-18  2016-05-30  EUR         2016-03-18
2016-01-18  2016-05-30  EUR         2016-04-18
2016-01-18  2016-05-30  EUR         2016-05-18

Months between two dates

DECLARE @StartDate  DATETIME,
        @EndDate    DATETIME;

SELECT   @StartDate = '20110501'        
        ,@EndDate   = '20110801';

SELECT  DATENAME(MONTH, DATEADD(MONTH, x.number, @StartDate)) AS MonthName
FROM    master.dbo.spt_values x
WHERE   x.type = 'P'        
AND     x.number <= DATEDIFF(MONTH, @StartDate, @EndDate);

Results:

MonthName
------------------------------
May
June
July
August

(4 row(s) affected)

Expanding Months Between Two Dates

You can use a recursive CTE:

with cte as (
      select Record, Startdate, EndDate, Type, startdate as dte
      from t
      union all
      select Record, Startdate, EndDate, Type, dateadd(month, 1, dte) as dte
      from cte
      where dte < enddate
     )
select Record, Startdate, EndDate, Type, datename(month, dte) + datename(year, dte)
from cte
order by record, dte;

Here is a db<>fiddle.

If you might have more than 100 months for a given record, then include option (maxrecursion 0).

List months between two dates in snowflake table

Try this one:

WITH Source_Table AS (

    SELECT ID, Dept_Name, TO_DATE(Start_Date, 'dd/MM/yyyy') AS Start_Date, TO_DATE(End_Date, 'dd/MM/yyyy') AS End_Date
    FROM (VALUES 
    (8797627, 'Dept1', '29/09/2021', '15/10/2021'),
    (8797627, 'Dept2', '29/09/2021', '27/12/2021'),
    (5489321, 'Dept1', '01/02/2022', '15/04/2022'),
    (5489321, 'Dept3', '01/01/2021', '01/02/2022')) T(ID, Dept_Name, Start_Date, End_Date)

), Gen_Month AS (

    SELECT DATEADD(MONTH, ROW_NUMBER() OVER(ORDER BY 1)-1, TO_DATE('01/01/2000', 'dd/MM/yyyy')) AS Months
      FROM TABLE(GENERATOR(ROWCOUNT => 1200)) AS v
)
SELECT *
  FROM Source_Table AS st
  LEFT JOIN Gen_Month AS gm ON gm.Months BETWEEN DATE_TRUNC(MONTH, st.Start_Date) AND DATE_TRUNC(MONTH, End_Date)
 ORDER BY ID, Dept_Name, Months

Result:

---