SQL bit by bit 5-generation time demention, mainly for time Conversion

The data warehouse contains the time table and storage time information. The specific information about the receiving start time and writing time of the stored procedure. The EXCEL function can quickly generate insert statements.

References

  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