SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE vTally (N int);
WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1 --10
         CROSS JOIN N N2 --100
         CROSS JOIN N N3 --1000
         CROSS JOIN N N4 --10000
         CROSS JOIN N N5 --100000
)
INSERT INTO vTally
SELECT I
FROM Tally;
100000 rows affected

SET STATISTICS TIME ON;

SELECT N,
       DATEADD(DAY,N-1,0) AS D,
       --FORMAT(DATEADD(DAY,N-1,0),'MM')
       RIGHT('00' + CONVERT(varchar(2),DATEPART(MONTH,DATEADD(DAY,N-1,0))),2) AS M
INTO #t1
FROM vTally;

SET STATISTICS TIME OFF;

 SQL Server Execution Times:
   CPU time = 110 ms,  elapsed time = 125 ms.
100000 rows affected

SET STATISTICS TIME ON;

SELECT N,
       DATEADD(DAY,N-1,0) AS D,
       FORMAT(DATEADD(DAY,N-1,0),'MM') AS M
       --RIGHT('00' + CONVERT(varchar(2),DATEPART(MONTH,DATEADD(DAY,N-1,0))),2)
INTO #t2
FROM vTally

SET STATISTICS TIME OFF;

 SQL Server Execution Times:
   CPU time = 3781 ms,  elapsed time = 4843 ms.
100000 rows affected