SQL - how to generate a date sequence

Have you ever needed to create a sequence of dates in SQL?
Here's a simple way to do it using CTE:

with dates(a_date) as (
    select convert(datetime, '20090801')
    union all
    select a_date + 1 from dates where a_date < '20090831' 
)
select a_date
from dates

This shall return a list of dates between 2009-08-01 and 2009-08-31. For example, it can be used when you're creating a month user activity report.

Let's say you have two tables: User and History. History table holds the user_id, login time and logoff time.

with dates(a_date) as (
    select convert(datetime, '20090801')
    union all
    select a_date + 1 from dates where a_date < '20090831' 
)

select
  u.username as username,
  d.a_date as a_date,
  min(h.login_time) as login_time,
  max(h.logoff_time) as logoff_time
from
  dates d
  cross join dbo.[User] u
  left join dbo.History h ON
    u.id = h.user_id and
    h.login_time >= d.a_date and
    h.login_time < dateadd(d, 1, d.a_date)       
group by
  u.username,
  d.a_date
order by
  u.username