Question on calendar week

Analytics
Enthusiast

Question on calendar week

Hi,

In sys_calendar.calendar the weeks start from sunday and end on saturday.
But I have a requirement where the weeks should start on monday and end on sunday (irrespective of the year).
Which means if the date is 2009-12-31 then its begin date should be 2009-12-28 and end date 2010-01-03.
and the next week should start from 2010-01-04 and end on 2010-01-10 and so on.

I wanted to know how we can achieve this in TD.

Thanks in advance.
1 REPLY
Enthusiast

Re: Question on calendar week

The Calendar is a view within the Sys_Calendar database.

The code below will rebase Day_Of_Week to start on Monday, not Sunday.

I suggest you set up a CALENDARTMPL (for Local) and a CalendarL view instead of replacing the existing Calendar/ CalendarTMP views. (Unless you want lots of callout when you next upgrade and forget to apply the change!)

(Updated to ensure week of month and year also reflect Monday being first day of week!)

REPLACE VIEW CALENDARTMPL(
calendar_date,
day_of_week,
day_of_month,
day_of_year,
day_of_calendar,
weekday_of_month,
week_of_month,
week_of_year,
week_of_calendar,
month_of_quarter,
month_of_year,
month_of_calendar,
quarter_of_year,
quarter_of_calendar,
year_of_calendar)
AS
SEL
calendar_date,
(day_of_calendar + 1) mod 7 + 1,
day_of_month,
day_of_year,
day_of_calendar,
(day_of_month - 1) / 7 + 1 ,
(day_of_month - (day_of_calendar + 1) mod 7 + 6) / 7,
(day_of_year - (day_of_calendar + 1) mod 7 + 6) / 7,
(day_of_calendar - (day_of_calendar + 1) mod 7 + 6) / 7,
(month_of_year - 1) mod 3 + 1,
month_of_year,
month_of_year + 12 * year_of_calendar,
(month_of_year + 2) / 3,
(month_of_year + 2) / 3 + 4 * year_of_calendar,
year_of_calendar + 1900
FROM CALBASICS;

REPLACE VIEW SYS_CALENDAR.CALENDARL (

calendar_date,

day_of_week,

day_of_month,

day_of_year,

day_of_calendar,

weekday_of_month,

week_of_month,

week_of_year,

week_of_calendar,

month_of_quarter,

month_of_year,

month_of_calendar,

quarter_of_year,

quarter_of_calendar,

year_of_calendar)

AS

SEL

calendar_date,

day_of_week,

day_of_month,

day_of_year,

day_of_calendar,

weekday_of_month,

week_of_month,

week_of_year,

week_of_calendar,

month_of_quarter,

month_of_year,

month_of_calendar,

quarter_of_year,

quarter_of_calendar,

year_of_calendar

FROM SYS_CALENDAR.CALENDARTMPL;