You need to use proper
data type[
^]. For sql server 2008 and higher it's a
date
data type.
Common Table Expression version:
;WITH MyDates AS
(
SELECT CONVERT(DATE, '2015-01-01') AS MyDate
UNION ALL
SELECT DATEADD(dd, 1, MyDAte) AS MyDate
FROM MyDates
WHERE MyDate<convert(date,>)
SELECT MyDate
FROM MyDates
OPTION (MAXRECURSION 0)