数据仓库中有时间表,存储时间信息,这个存储过程接收开始时间结束时间,写入时间具体信息。有高手用excel函数功能很快能产生INSERT语句不会啊,只能用这个。
参考知识
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
|
vSET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* -------------------------------------------------------- *generate Date demention *@startdate '01/01/2010' *@enddate '12/30/2010' ---------------------------------------------------------*/ CREATE PROCEDURE dbo.AddDateTime @startdate datetime, @enddate datetime AS declare @i int set @i=1 while(DATEDIFF(DD,@startdate,@enddate)>=0) begin insert into [ReportServer].[dbo].[D_DATE]( [DATE_KEY], --primarykey [ DATE ], --datetime [FULL_DATE_DESCRIPTION], --detail date [DAY_OF_WEEK], --day of week from 1 to 6 [CALENDAR_MONTH], --month of year from 1 to 12 [CALENDAR_YEAR], --year [FISCAL_YEAR_MONTH], --fiscal year [HOLIDAY_INDICATOR], --holiday iden [WEEKDAY_INDICATOR]) select @i, CONVERT ( varchar (10),@startdate,101), DATENAME(mm,@startdate)+ ' ' +DATENAME(DD,@startdate)+ ', ' +DATENAME(YYYY,@startdate), DATEPART(DW,@startdate), DATEPART(MM,@startdate), DATEPART(YYYY,@startdate), 'F' + CONVERT ( varchar (7),@startdate,23), case 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 else 0 end , case when (DATENAME(DW,@startdate) in ( 'Saturday' , 'Sunday' )) then 1 else 0 end if DATEDIFF(DD,@startdate,@enddate)>=0 begin set @startdate = DATEADD(dd,1,@startdate) set @i=@i+1 continue end else begin break end end |
作者:Tyler Ning
出处:http://www.cnblogs.com/tylerdonet/