Hi,

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

UNION ALL

SELECT

TimeKey = CONVERT(INT,(CONVERT(NVARCHAR(10),FullDate + 1,112))),

FullDate = FullDate + 1

FROM DateCTE

WHERE FullDate + 1 < = @EndDate

)

SELECT * FROM DateCTE

OPTION (MAXRECURSION 0);

I hope it will help.