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
