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

时间：2021-09-12 17:59:12

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

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

参考知识


 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

相关文章