Разбивка периода по месяцам или часам

Разбить период с 18.09.2012 по 24.01.2013 помесячно можно следующим запросом, например:

declare @firstdate datetime, @lastdate datetime
select @firstdate = '20120918', @lastdate = '20130124'

select
case when d1>@firstdate then d1 else @firstdate end as d1,
case when d2<@lastdate then d2 else @lastdate end as d2
from
(
select
dateadd(mm,datediff(mm,'19000101',@firstdate)+number,'19000101') as d1,
dateadd(mm,datediff(mm,'19000101',@firstdate)+number+1,'19000101')-1 as d2
from master..spt_values
where type='P'
and number<=datediff(mm,@firstdate,@lastdate)
) ttt

Результат:


2012-09-18 00:00:00.000 2012-09-30 00:00:00.000
2012-10-01 00:00:00.000 2012-10-31 00:00:00.000
2012-11-01 00:00:00.000 2012-11-30 00:00:00.000
2012-12-01 00:00:00.000 2012-12-31 00:00:00.000
2013-01-01 00:00:00.000 2013-01-24 00:00:00.000

Источник: http://www.sql.ru/forum/actualthread.aspx?tid=599280&pg=1&mid=6234623#6234623


И еще аналогично по часам:


declare @firstdate datetime, @lastdate datetime
select @firstdate = dateadd(hh, -24, getdate()), @lastdate = getdate()

select
case when d1>@firstdate then d1 else @firstdate end as d1,
case when d2<@lastdate then d2 else @lastdate end as d2
from
(
select
dateadd(hh,datediff(hh,'19000101',@firstdate)+number,'19000101') as d1,
dateadd(hh,datediff(hh,'19000101',@firstdate)+number+1,'19000101') as d2
from master..spt_values
where type='P'
and number<=datediff(hh,@firstdate,@lastdate)
) ttt