for the last couple of hours I have been breaking my head over this.

I want to create a result set which contains a series of dates like this:

```
2011-07-05
2011-07-04
2011-07-03
2011-07-02
2011-07-01
2011-06-30
2011-06-29
2011-06-28
...

```

Ideally between 2 dates given. But If I can say the last 30 days or the last 100 days from now that would be fine also.

Normally I would this with a CTE like this

```
;WITH Dates AS
(
    SELECT CONVERT(DATE, GETDATE()) [Date]
    UNION ALL
    SELECT DATEADD(DAY,-1, [Date])
    FROM Dates
    WHERE [Date] > DATEADD(DAY, -30, CONVERT(DATE, GETDATE()))
)
SELECT [Date]

```

But I am not allowed to use any statements that can't be executed in a subquery. The program I am using executes queries like this:

```
Select *
From (
    TheQuery
) as t1

```

This means I can't use declares, no stored procedures, no CTEs..

Is there any way I can obtain the dataset I need with these limitations?

I am using azure SQL