Sql Serverが連続日を生成する方法のまとめ

1395 ワード

1つ目はmasterを利用する..spt_連続日を生成するvalues
declare @date datetime
set @date='2009-02-05'
select [day]=convert(varchar,DATEADD(mm,DATEDIFF(mm,0,@date),0)+number,121)
from master..spt_values
where type='p'
and number< datediff(dd,@date,dateadd(mm,1,@date))
--  2008          :
create table Mon
(
ID int identity(1,1),
Mon varchar(6)
)
GO
DECLARE
@BeginMonth varchar(6),
@EndMonth varchar(6)
SELECT
@BeginMonth='200801',
@EndMonth='200906'
INSERT Mon(Mon)
SELECT
CONVERT(VARCHAR(6),DATEADD(month,number,@BeginMonth+'01'),112)
FROM
master..spt_values
WHERE
type='P'
and
DATEADD(month,number,@BeginMonth+'01')<=@EndMonth+'01'
SELECT * FROM Mon
DROP TABLE Mon
      master..sp_values       ,      2048  ,   2048 
  
select number from
master..spt_values
where type='P' and number between 1 and 1000

2つ は、 を し、テーブルtemptab(date)は テーブルにすぎない
Declare @mindate datetime
Declare @maxdate datetime
set @mindate  = '2010-01-01'
set @maxdate  = '2010-02-01'
;with temptab(date) as
( select @mindate
union all
select dateadd(d,1,temptab.date) as date
from temptab
where dateadd(d,1,temptab.date)<=@maxdate
)
select * from temptab
http://www.cnblogs.com/envelope/archive/2010/04/27/1721786.html