(reposted from other thread)

Your best bet is to use a TALLY table, a CTE, or at worst a Recursive CTE.

using an crude tally table

With Cte_n

AS

(

Select 1 a Union ALL Select 1 Union ALL Select 1 Union ALL Select 1 Union ALL Select 1

UNION ALL Select 1 a Union ALL Select 1 Union ALL Select 1 Union ALL Select 1 Union ALL Select 1

),Cte_n1

AS

(

Select n1.a a from CTE_n n, Cte_n n1,Cte_n n2

),

Cte_Tally

AS

(

Select Row_Number() OVER (ORDER BY a) a from Cte_n1

)

Select

DateAdd(d,a-1,'01-Jan-1900')

From Cte_Tally

Working out the date parts year, months etc should be relatively simple.