These are the points based on that you may getting error meesage:
1. DATEFORMAT is set to MDY and you are storing as date in DMY format.
2. You may use OPTION (MAXRECURSION 0) opetion to avoid following error message, as default recursion is 100.
Msg 530, Level 16, State 1, Line 5
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
Please check following, it should work for you.
SET DATEFORMAT DMY;
DECLARE @StartDate DATETIME = '01-01-2010';
DECLARE @EndDate DATETIME = '31-12-2010';
WITH DateCTE AS
SELECT TimeKey = CONVERT(INT,(CONVERT(VARCHAR(10),@StartDate,112))),
FullDate = @StartDate
TimeKey = CONVERT(INT,(CONVERT(NVARCHAR(10),FullDate + 1,112))),
FullDate = FullDate + 1
WHERE FullDate + 1 < = @EndDate
SELECT * FROM DateCTE
OPTION (MAXRECURSION 0);
I hope it will help.