There are three ways of personal understanding

1. Obtained from the system table Master.. SPT ﹣ values
2. Get with WHILE loop
3. Cursor acquisition

Method 1: obtain from the system table master..spt_values

1. Obtain consecutive days

-- Get the date of continuous interval
DECLARE @StartTime DATE = '2019-03-08', -- start time
        @EndTime   DATE = '2019-03-18' -- End time

SELECT CONVERT(NVARCHAR(10),DATEADD(DAY,number,@StartTime),120) AS DayTime
  FROM master..spt_values  
 WHERE type = 'p' 
   AND number <= DATEDIFF(DAY,@StartTime,@EndTime)

 
2. Obtain consecutive months

-- Obtain the month of continuous interval
DECLARE @StartTime DATE = '2019-03-08', -- start time
        @EndTime   DATE = '2019-12-28' -- End time

SELECT CONVERT(VARCHAR(7),DATEADD(MONTH,number,@StartTime),120) AS MonthTime
  FROM master..spt_values WITH (NOLOCK) 
 WHERE type='P' 
   AND number <= DATEDIFF(MONTH,@StartTime,@EndTime)

3. Obtain consecutive years

-- Get year of continuous interval
DECLARE @StartTime DATE = '2019-03-08', -- start time
        @EndTime   DATE = '2020-03-08' -- End time

SELECT CONVERT(VARCHAR(4),DATEADD(YEAR,number,@StartTime),120) AS YearTime
  FROM master..spt_values WITH (NOLOCK) 
 WHERE type='P' 
   AND number <= DATEDIFF(YEAR,@StartTime,@EndTime)

Principle of master..spt_values:

Number is used to add and subtract month, year and day. Because the maximum number value is 2047, only 2047 can be added continuously.

As shown in the figure: the end time is' 2026-03-28 ', but we can see in descending order that only 2047 consecutive days can be added from 2019-03-08, so there will be no date after 2024-10-24.

Therefore, if the continuous interval exceeds 2048, this method is not applicable. At this time, the following method 2 can be used

Method 2: obtain with WHILE loop

1. Obtain consecutive days

-- Consecutive days
DECLARE @StartTime DATE = '2019-03-08', -- start time
        @EndTime   DATE = '2019-03-18' -- End time

-- Create temporary table#DateTime store date
CREATE TABLE #DateTime
(
    DayTime DATE
);

-- Loop get date insert temporary table
WHILE @StartTime <= @EndTime
BEGIN
    INSERT INTO #DateTime (DayTime)
    VALUES (@StartTime);
    SET @StartTime = DATEADD(DAY, 1, @StartTime);
END;

SELECT DayTime FROM #DateTime;

-- Delete temporary table
DROP TABLE #DateTime;

2. Obtain consecutive months

-- Consecutive months
DECLARE @StartTime DATE = '2019-03-08', -- start time
        @EndTime   DATE = '2019-12-28' -- End time

-- Create temporary table#MonthTime storage date
CREATE TABLE #MonthTime
(
    MonthTime VARCHAR(7)
);



-- Loop get date insert temporary table
WHILE @StartTime <= @EndTime
BEGIN
    INSERT INTO #MonthTime (MonthTime)
    VALUES (CONVERT(VARCHAR(7),@StartTime));
    SET @StartTime = DATEADD(MONTH, 1, @StartTime);
END;

SELECT MonthTime FROM #MonthTime;

-- Delete temporary table
DROP TABLE #MonthTime;

3. Obtain consecutive years

-- Consecutive months
DECLARE @StartTime DATE = '2019-03-08', -- start time
        @EndTime   DATE = '2025-03-28' -- End time

-- Create temporary table#YearTime storage date
CREATE TABLE #YearTime
(
    YearTime VARCHAR(4)
);



-- Loop get date insert temporary table
WHILE @StartTime <= @EndTime
BEGIN
    INSERT INTO #YearTime (YearTime)
    VALUES (CONVERT(VARCHAR(4),@StartTime));
    SET @StartTime = DATEADD(YEAR, 1, @StartTime);
END;

SELECT YearTime FROM #YearTime;

-- Delete temporary table
DROP TABLE #YearTime;

Method 3: get with cursor, the principle is similar to the WHILE loop, which will not be shown here

If there are any good methods and suggestions, welcome to give advice, thank you!