sql生成一个日期表

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  lidongbo
-- Create date: <Create Date,,>
-- Description: 生成日期表，为营业统计使用
-- =============================================
CREATE PROCEDURE CreateDateTable
 -- Add the parameters for the stored procedure here
 @p_startTime datetime,
 @p_endTime datetime
AS
BEGIN
 /************生成时间临时表********/
create table #etTtime_TempTable (
 [ID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
        [日期列] DateTime,
[门店编号列] varchar(50)
)
declare @etKdd int,@etKmmStr nvarchar(13),@etKddStr nvarchar(13),@etKwwStr nvarchar(13),@etKqqStr nvarchar(13),@start_Time datetime,@end_Time datetime,@type_Time int,@mdbh varchar(50)
--set @start_Time='2010-01-01'/*设置起始时间*/
--set @end_Time='2100-12-31'/*设置终止时间*/
set @start_Time=@p_startTime/*设置起始时间*/
set @end_Time=@p_endTime/*设置终止时间*/
set @type_Time=1   /*生成类型 1为天 2为周 3为月 4为季度 5为年*/
set @mdbh='zlwy4000480006'
WHILE @start_Time<@end_Time /**/
begin
set @etKdd=datepart(dd,@start_Time)
set @etKddStr=cast(@etKdd as nvarchar(13))

set @etKdd=datepart(mm,@start_Time)
set @etKmmStr=cast(@etKdd as nvarchar(13))

set @etKdd=datepart(ww,@start_Time)
set @etKwwStr=cast(@etKdd as nvarchar(13))

set @etKdd=datepart(qq,@start_Time)
set @etKqqStr=cast(@etKdd as nvarchar(13))

if @type_Time=1
Begin
INSERT INTO #etTtime_TempTable (日期列,门店编号列) VALUES (cast(datepart(yy,@start_Time)as nvarchar(13))+'-'+@etKmmStr+'-'+@etKddStr,@mdbh)
SET @start_Time=DATEADD(dd,1,@start_Time)
End
if @type_Time=2
Begin
INSERT INTO #etTtime_TempTable (日期列,门店编号列) VALUES (cast(datepart(yy,@start_Time)as nvarchar(13))+'-'+@etKwwStr,@mdbh)
SET @start_Time=DATEADD(ww,1,@start_Time)
End
if @type_Time=3
Begin
INSERT INTO #etTtime_TempTable (日期列,门店编号列) VALUES (cast(datepart(yy,@start_Time)as nvarchar(13))+'-'+@etKmmStr,@mdbh)
SET @start_Time=DATEADD(mm,1,@start_Time)
End
if @type_Time=4
Begin
INSERT INTO #etTtime_TempTable (日期列,门店编号列) VALUES (cast(datepart(yy,@start_Time)as nvarchar(13))+'-'+@etKqqStr,@mdbh)
SET @start_Time=DATEADD(qq,1,@start_Time)
End
if @type_Time=5
Begin
INSERT INTO #etTtime_TempTable (日期列,门店编号列) VALUES (cast(datepart(yy,@start_Time)as nvarchar(13)),@mdbh)
SET @start_Time=DATEADD(yy,1,@start_Time) /*循环开始时间加1 直到等于设置的终止时间*/
End

Continue
End

/*查询临时表*/
--Select * From #etTtime_TempTable
--delete from datetable
insert into datetable select id, 日期列 from  #etTtime_TempTable
/*删除临时表*/
Drop Table #etTtime_TempTable
END
GO

转载于:https://www.cnblogs.com/goto/p/3326579.html