How do I set month start and end date to 25th and 26th respectively for each year using T-SQL

declare @dateTable table ( id int identity(1,1), sDate date, eDate date)

declare @min int = 1 , @max int = 12
declare @sdate date = '2020-01-01'
declare @edate date = '2020-01-25'
declare @eDateTm datetime
declare @sDateTm datetime

while @min <= @max
begin 
if @min = 100
	begin
		break;
	end
	
	if @min > 1
	begin
		if month(@sdate) < 11
		begin
		set @eDateTm = dateAdd(MM, 1 , @sdate)
		set @eDateTm = dateAdd(dd, -1 , @eDateTm)
		set @edate = cast(@eDateTm as date)
		
		insert into @dateTable 
		select @sdate , @edate

		set @sDateTm = dateAdd(dd, +1 , @eDateTm)
		set @sdate = cast(@sDateTm as date)
		end
		else
		begin
			set @edate = '2020-12-31'
			insert into @dateTable 
			select @sdate , @edate
		end
	end
	else
	begin
		insert into @dateTable 
		select @sdate , @edate
		set @sDateTm = dateAdd(dd, +1 , @eDate)
		set @sdate = cast(@sDateTm as date)

	end
	set @min = @min + 1
	--select @min

end

select * from @dateTable

wizy@2020 wrote:

Good Maciej.
What I want to really achieve is to have the following results
e.g

RecDate MonthNo
2020-01-01 1
2020-01-02 1
...
2020-01-25 1
2020-01-26 2
...
2020-02-25 2
2020-02-26 3
...
up to
2020-12-31 12

I have a table with over 20000 records and want to at any point get the correct month number. This should be able to work with any year.

--initial range
DECLARE @initstartdate DATE = CAST('2019-01-01' AS DATE); --define initial start date
DECLARE @initenddate DATE = CAST('2020-12-31' AS DATE); --define initial end date
--declare and fill-in reference table to store fiscal year details
DECLARE @reftable TABLE(refdayfrom INT, refmonthfrom INT, refdayto INT, refmonthto INT, fiscalmonth INT)
INSERT INTO @reftable(refdayfrom, refmonthfrom, refdayto, refmonthto, fiscalmonth)
VALUES(1, 1, 25, 1, 1),
(26, 1, 25, 2, 2),
(26, 2, 25, 3, 3),
(26, 3, 25, 4, 4),
(26, 4, 25, 5, 5),
(26, 5, 25, 6, 6),
(26, 6, 25, 7, 7),
(26, 7, 25, 8, 8),
(26, 8, 25, 9, 9),
(26, 9, 25, 10, 10),
(26, 10, 25, 11, 11),
(26, 11, 31, 12, 12)

--get all dates between initial start date and end date
;WITH alldates AS
(
	SELECT @initstartdate RecDate
	UNION ALL
	SELECT DATEADD(DD, 1, RecDate) RecDate 
	FROM alldates
	WHERE DATEADD(DD, 1, RecDate)<=@initenddate 
),
--get fiscal year details for years between initial start date and end date
	dateranges AS
	(
		--initial query
		SELECT DATEFROMPARTS(YEAR(@initstartdate), refmonthfrom, refdayfrom ) startdate, DATEFROMPARTS(YEAR(@initstartdate), refmonthto, refdayto) enddate, fiscalmonth
		FROM @reftable
		UNION ALL
		----recursive part
		SELECT DATEFROMPARTS(YEAR(DATEADD(YY, 1, startdate)), MONTH(startdate), DAY(startdate)) startdate, 
			DATEFROMPARTS(YEAR(DATEADD(YY, 1, enddate)), MONTH(enddate), DAY(enddate)) enddate, fiscalmonth
		FROM dateranges 
		WHERE DATEADD(YY, 1, enddate)<= @initenddate 
	)
-- mix all together ;)
	SELECT ad.RecDate, dr.fiscalmonth MonthNo
	FROM alldates ad INNER JOIN dateranges dr ON ad.RecDate BETWEEN dr.startdate AND dr.enddate 
	ORDER BY RecDate
	OPTION (MAXRECURSION 0);

RecDate	MonthNo
2019-01-01	1
up to
2020-12-31	12