All Forums Analytics
adash-7422 33 posts Joined 01/09
08 Jul 2009
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.

Jimm 298 posts Joined 09/07
09 Jul 2009

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)ASSEL 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 + 1900FROM 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)ASSEL 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_calendarFROM SYS_CALENDAR.CALENDARTMPL;

You must sign in to leave a comment.