Need help to tweak create date dimension script

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need help to tweak create date dimension script

Author

Topic

sharath_ms
Starting Member

3 Posts

Posted - 2013-03-14 : 09:11:02

Hello folks,

The below script for creating time dimension.In the output along with current fields i need onemore field in this format " 1996-07-04 00:00:00.000" for all days in the year.

It will be great if someone can tweak the script to include additional date field which will result in above requested date format.

Thanks for your help in advance.

Listing 1
------------
--drop table DimDate
if object_id('DimDate') IS NOT NULL
drop table DimDate
create table DimDate(
DateKey int NOT NULL
,DayOfMonth tinyint NULL
,MonthOfYear tinyint NULL
,[Year] smallint NULL
,[DayOfWeek] varchar(15) NULL
,WeekOfYear tinyint NULL
,[DayOfYear] smallint NULL
,[MonthName] varchar(20) NULL
,[Quarter] tinyint NULL
,QuarterName varchar(20) NULL
,IsLastDayOfMonth char(1) NULL
,IsHoliday char(1) NULL
,HolidayName varchar(50) NULL
,HolidayDescription varchar(150) NULL
)

GO
Listing 2
----------
declare @startDate as datetime,@enddate as datetime
--initialize
set @startDate = '01/01/2007'
set @enddate = '01/01/2009'

while @startDate<@enddate
begin
insert into DimDate(
DateKey
,[DayOfMonth]
,MonthOfYear
,[Year]
,[DayOfWeek]
,WeekOfYear
,[DayOfYear]
,[MonthName]
,[Quarter]
,QuarterName)
select cast(convert(varchar(8),@startdate,112) as int)
,day(@startdate)
,Month(@startdate)
,Year(@startdate)
,datename(weekday,@startdate) as WeekDay
,datename(week,@startdate) as Week
,datename(dayOfYear,@startdate) as dayOfYear
,datename(month,@startdate) as MonthName
,datename(quarter,@startdate) as Quarter
,CASE datename(quarter,@startdate)
WHEN 1 THEN 'First Quarter'
WHEN 2 THEN 'Second Quarter'
WHEN 3 THEN 'Third Quarter'
WHEN 4 THEN 'Fourth Quarter'
END AS QuarterName

set @startDate = @startDate +1
end
GO

Listing 3
------------

CREATE FUNCTION fnGetLastDayOfMonth
-- Input parameters
(
@Anydate datetime
)
RETURNS datetime AS
/********************************************************************
Returns the last day of the month (extracted from the date passed)
*********************************************************************/
BEGIN
-- add one month to the datepassed
SET @Anydate = DATEADD(m,1,@Anydate)
RETURN DATEADD(d,-datepart(d,@Anydate),@Anydate)
END

GO
--Update the column IsLastDayOfMonth
UPDATE
DimDate
SET
IsLastDayOfMonth = CASE WHEN dbo.fnGetLastDayOfMonth(cast(DateKey as varchar(10)))= Cast(Cast(DateKey as varchar(10)) as datetime) THEN 'Y'
ELSE 'N' END

GO
select * from DimDate "

Thanks
SSM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources