SQL point-by-point generation time demention, mainly time Conversion

Editor's note: q a: Where did the experience come from? Answer: experience comes from practice and from some notes in life. Learn to sum up, learn to pay attention, learn to discover... you can also be reborn, jumping off the sublimation... following the author, we know how to filter the information of Data columns, simple functions for string splitting, and the role of the SET QUOTED_IDENTIFIER OFF statement. Now we will continue to follow the author and share his SQL details. Now we will start ....

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.

Please refer to the following link for more information:

 
 

  
  
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