SQL Fiscal Calendar Build Script

If you are an Oracle BI pro then you know that you can grab a better calendar from the BI Apps time dimensions. If you are SQL Server AS pro then you know that you can get a better calendar built by the time dimension wizard. However, if you are just looking for a quick and dirty calendar based a start and stop timeframe from which you have complete control, etc. then this will be a helpful start for your mission. Let me know if it helps. This was written originally for a SQL Server database but clearly just adopt it for Oracle by changing it to the related PL/SQL syntax.

view sourceprint?

001.--/////////////////////////////////////////////////////////

002.-- calendarBasic

003.--/////////////////////////////////////////////////////////

004. 

005.IF OBJECT_ID('calendarbasic') IS NOT NULL

006.DROP TABLE [dbo].[calendarBasic]

007.GO

008. 

009.CREATE TABLE [dbo].[calendarBasic]

010.(

011.dateID [int] NOT NULL PRIMARY KEY CLUSTERED,

012.[fullDate] datetime NOT NULL,

013.[dateName] [char](10) NOT NULL,

014.yearMonth int,

015.yearWeek int,

016.[YQMD] [char](10) NOT NULL,

017.[dayOfWeek] [int] NOT NULL,

018.[dayOfWeekName] [varchar](10) NOT NULL,

019.[dayOfMonth] [int] NOT NULL,

020.[monthName] [varchar](10) NOT NULL,

021.[monthAbbr] [char](3) NOT NULL,

022.[dayOfYear] [int] NOT NULL,

023.[isWeekDay] [bit] NOT NULL,

024.[isWeekEndDay] [bit] NOT NULL,

025.[isHoliday] [bit] NOT NULL,

026.[weekOfYear] [int] NOT NULL,

027.[monthOfYear] [int] NOT NULL,

028.[isLastDayOfMonth] [bit] NOT NULL,

029.[calendarQuarter] [int] NOT NULL,

030.[calendarSemester] [int] NOT NULL,

031.[calendarYear] [int] NOT NULL,

032.[fiscalMonthOfYear] [int] NOT NULL,

033.[fiscalQuarter] [int] NOT NULL,

034.[fiscalSemester] [int] NOT NULL,

035.[fiscalYear] [int] NOT NULL

036.)

037.GO

038. 

039.DECLARE @ProcessDate datetime

040.SELECT @ProcessDate = '2000-01-01' --initialize

041. 

042.WHILE @ProcessDate < = '2030-12-31'

043.BEGIN

044.--SELECT @ProcessDate --display

045. 

046.INSERT [dbo].[calendarBasic]

047.(

048.[dateID],

049.[FullDate],

050.[DateName],

051.yearMonth,

052.yearWeek,

053.[YQMD],

054.[DayOfWeek],

055.[DayOfWeekName],

056.[DayOfMonth],

057.[MonthName],

058.[MonthAbbr],

059.[DayOfYear],

060.[IsWeekDay],

061.[IsWeekEndDay],

062.[IsHoliday],

063.[WeekOfYear],

064.[MonthOfYear],

065.[IsLastDayOfMonth],

066.[CalendarQuarter],

067.[CalendarSemester],

068.[CalendarYear],

069.[FiscalMonthOfYear],

070.[FiscalQuarter],

071.[FiscalSemester],

072.[FiscalYear]

073.)

074.SELECT

075.CONVERT(int, CONVERT(char(8), @ProcessDate, 112)) AS [dateID],

076.CONVERT(datetime, @ProcessDate, 101) AS [FullDate],

077.CONVERT(char(10), @ProcessDate, 126) AS [DateName],

078.CONVERT(int, LEFT(CONVERT(char(6), @ProcessDate, 112), 6)) AS yearMonth,

079.CONVERT(int, LEFT(CONVERT(char(4), @ProcessDate, 112), 6) + RIGHT('00' +CONVERT(varchar(2), DATEPART(wk, @ProcessDate)), 2)) AS yearWeek,

080. 

081.CONVERT(char(10), CONVERT(char(4), DATEPART(yy, @ProcessDate)) +

082.N'0' + CONVERT(char(1), DATEPART(qq, @ProcessDate)) +

083.CASE WHEN LEN(DATEPART(mm, @ProcessDate)) < 2 THEN N'0' + CONVERT(char(1), DATEPART(mm, @ProcessDate)) ELSE + CONVERT(char(2), DATEPART(mm, @ProcessDate)) END+

084.CASE WHEN LEN(DATEPART(dd, @ProcessDate)) < 2 THEN N'0' + CONVERT(char(1), DATEPART(dd, @ProcessDate)) ELSE + CONVERT(char(2), DATEPART(dd, @ProcessDate)) END)AS [YQMD],

085.CONVERT(int, DATEPART(dw, @ProcessDate)) AS [DayOfWeek],

086.CONVERT(varchar(10), DATENAME(dw, @ProcessDate)) AS [DayOfWeekName],

087.CONVERT(int, DATEPART(dd, @ProcessDate)) AS [DayOfMonth],

088.CONVERT(varchar(10), DATENAME(mm, @ProcessDate)) AS [MonthName],

089.CONVERT(char(3), DATENAME(mm, @ProcessDate)) AS [MonthAbbr],

090.CONVERT(int, DATEPART(dy, @ProcessDate)) AS [DayOfYear],

091.CONVERT(bit, CASE WHEN DATENAME(dw, @ProcessDate) NOT IN (N'Saturday', N'Sunday') THEN 1ELSE 0 END) AS [IsWeekDay],

092.CONVERT(bit, CASE WHEN DATENAME(dw, @ProcessDate) IN (N'Saturday', N'Sunday') THEN 1ELSE 0 END) AS [IsWeekEndDay],

093.CONVERT(bit, 0) AS [IsHoliday],

094.CONVERT(int, DATEPART(wk, @ProcessDate)) AS [WeekOfYear],

095.CONVERT(int, DATEPART(mm, @ProcessDate)) AS [MonthOfYear],

096.CONVERT(bit, CASE WHEN DATEPART(mm, @ProcessDate) <> DATEPART(mm, DATEADD(dd, 1, @ProcessDate)) THEN 1 ELSE 0 END) AS [IsLastDayOfMonth],

097.CONVERT(int, DATEPART(qq, @ProcessDate)) AS [CalendarQuarter],

098.CONVERT(int, CASE WHEN DATEPART(mm, @ProcessDate) < 7 THEN 1 ELSE 2 END) AS[CalendarSemester],

099.CONVERT(int, DATEPART(yy, @ProcessDate)) AS [CalendarYear],

100.CONVERT(int, DATEPART(mm, @ProcessDate)) AS [FiscalMonthOfYear],

101.CONVERT(int, DATEPART(qq, @ProcessDate)) AS [FiscalQuarter],

102.CONVERT(int, CASE WHEN DATEPART(mm, @ProcessDate) < 7 THEN 1 ELSE 2 END) AS[FiscalSemester],

103.CONVERT(int, DATEPART(yy, @ProcessDate)) AS [FiscalYear]

104. 

105.SELECT @ProcessDate = DATEADD(dd, 1, @ProcessDate) --increment

106. 

107.END

108.GO

109. 

110.--SELECT * FROM calendarBasic