当前位置:首页 > 博客园新文 >SQL点滴5—产生时间demention,主要是时间转换

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

  作者:凌空11腿   来源: 博客园-lonelyxmas  
原文: 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

相关阅读:

来源:(微信/QQ:420434200,微信公众号:makaidong-com)
       欢迎分享本文,转载请保留出处!

       【原文阅读】:http://www.cnblogs.com/lonelyxmas/p/4140406.html
没有了