SQL点滴5—产生时间demention，主要是时间转换

数据仓库中有时间表，存储时间信息，这个存储过程接收开始时间结束时间，写入时间具体信息。有高手用excel函数功能很快能产生INSERT语句不会啊，只能用这个。

参考知识

 1 vSET ANSI_NULLS ON
 2  GO
 3 
 4  SET QUOTED_IDENTIFIER ON
 5 GO
 6 
 7 /*--------------------------------------------------------
 8 *generate Date demention
 9 *@startdate '01/01/2010'
10 *@enddate   '12/30/2010'
11 ---------------------------------------------------------*/
12 
13 CREATE PROCEDURE dbo.AddDateTime
14 @startdate datetime,
15 @enddate datetime
16 AS
17   declare @i int
18   set @i=1
19   
20   while(DATEDIFF(DD,@startdate,@enddate)>=0)
21   begin
22   
23   insert into [ReportServer].[dbo].[D_DATE](
24     [DATE_KEY],                  --primarykey
25     [DATE],                      --datetime
26     [FULL_DATE_DESCRIPTION],     --detail date
27     [DAY_OF_WEEK],               --day of week from 1 to 6
28     [CALENDAR_MONTH],            --month of year from 1 to 12
29     [CALENDAR_YEAR],             --year
30     [FISCAL_YEAR_MONTH],         --fiscal year
31     [HOLIDAY_INDICATOR],         --holiday iden
32     [WEEKDAY_INDICATOR])
33   select
34   @i,
35   CONVERT(varchar(10),@startdate,101),
36   DATENAME(mm,@startdate)+' '+DATENAME(DD,@startdate)+', '+DATENAME(YYYY,@startdate),
37   DATEPART(DW,@startdate),
38   DATEPART(MM,@startdate), 
39   DATEPART(YYYY,@startdate),
40   'F'+ CONVERT(varchar(7),@startdate,23), 
41   case 
42   when( CONVERT(varchar(5),@startdate,101) in ('01/01','01/05','02/05','03/05','01/10','02/10','03/10','04/10','05/10','06/10') ) then  1 
43   else 0 end,
44   case
45   when(DATENAME(DW,@startdate) in ('Saturday','Sunday')) then 1
46   else 0 end
47    
48   if DATEDIFF(DD,@startdate,@enddate)>=0
49   
50   begin
51   set @startdate = DATEADD(dd,1,@startdate)
52   set @i=@i+1
53   continue
54   end
55   
56   else
57   
58   begin
59   break
60   end
61   
62   end
posted @ 2010-12-17 18:04  nd  阅读(1062)  评论(0)  编辑  收藏  举报