sql存储过程生成dim_date
USE [Config]
GO
/****** Object: StoredProcedure [dbo].[getDimDate] Script Date: 08/16/2016 16:07:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[getDimDate]
@begin_date varchar(10),
@end_date varchar(10)
as
begin
declare @dayNum int,@yearNum int,@monthNum int,@weekNum int,@weekdayNum int,@quarterNum int,@i int,@tempdate datetime
set @i=0
while @i<=(select datediff(day,convert(smalldatetime,@begin_date,101),convert(smalldatetime,@end_date,101)))
begin
set @tempdate=dateadd(day,@i,convert(smalldatetime,@begin_date,101))
set @monthNum=datepart(month,@tempdate)
set @weekNum=datepart(week,@tempdate)
--set @weekdayNum=datepart(weekday,@tempdate)
set @quarterNum=datepart(quarter,@tempdate)
set @yearNum=datepart(year,@tempdate)
set @dayNum=datepart(day,@tempdate)
insert into dbo.dim_DATE(idx,date_id,dayNum,monthNum,weekNum,quarterNum,yearNum)values
(newid(),@tempdate,@dayNum,@monthNum,@weekNum,@quarterNum,@yearNum)
set @i=@i+1
end
end
转载请注明原文地址: https://ju.6miu.com/read-1310904.html
