SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need help to tweak create date dimension script
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sharath_ms
Starting Member

3 Posts

Posted - 03/14/2013 :  09:11:02  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000