All Forums Database
Arbie 1 post Joined 03/12
21 Mar 2012
Fiscal vs. Calendar Issue - Adjusting SYS_CALENDAR to Begin Monday

Good afternoon,

I'm currently trying to roll-up daily customer transaction data to the weekly level. I've joined my dataset to Teradata's SYS_CALENDAR.CALENDAR, and have used the 'WEEK_OF_YEAR' to bring daily data to the week level.

My issue is that I need to roll-up daily data beginnging on the first day of my company's fiscal year, which is a Monday. Is there any way to adjust the SYS_CALENDAR to consider the first day of a week as a Monday, not Sunday?

Does anyone have any alternative ideas as to how I might solve this issue?

I'm new to database management, so the more detail you are all able to provide, the better!

With thanks,


ulrich 816 posts Joined 09/09
22 Mar 2012

The question is if this change would be already sufficient for you?

How is the fiscal week definition in your company for the week which contains the 01.01. of a year?

Many customers have a separate calendar table where they are able to maintain specifics of theire country and company.


feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

29 Nov 2012

I need to design a calendar view in which week should be Monday to Sunday whereas in Current Teradata calendar it's Sunday to Monday.
I am done with logic for DAY_OF_WEEK column but facing difficulty in WEEK_OF_YEAR column , here also week should be considered from Monday to Sunday and also boundry days are to be considered e.g. if first day of  sunday then it should come in last year's last week.
Please suggest.

dnoeth 4628 posts Joined 11/04
29 Nov 2012

What definition of week are you trying to implement? ISO?
This is what i use:

REPLACE VIEW sys_calendar.calbasics_iso AS
/*** Modify here for different start date ***/
  DATE '1900-01-01' AS start_of_calendar,

  cdate AS calendar_date,

  ((cdate - DATE '0001-01-01') MOD 7) + 1 (FORMAT '9') AS day_of_week,

  EXTRACT(DAY FROM cdate) (FORMAT '99') AS day_of_month,

  cdate - ((EXTRACT(YEAR FROM cdate) - 1900) * 10000 + 0101 (DATE)) + 1 (FORMAT '9999') AS day_of_year,

  cdate - start_of_calendar + 1 AS day_of_calendar,

  (day_of_month - 1) / 7 + 1 (FORMAT '9') AS WEEKDAY_OF_MONTH,

  (day_of_month - day_of_week + 6) / 7 (FORMAT '9') AS week_of_month,

  (day_of_calendar - day_of_week + 6) / 7 AS week_of_calendar,

  (month_of_year - 1) MOD 3 + 1 AS month_of_quarter,

  EXTRACT(MONTH FROM cdate) AS month_of_year,

  month_of_year + 12 * (year_of_calendar - EXTRACT(YEAR FROM start_of_calendar)) AS month_of_calendar,

  (month_of_year + 2) / 3 AS quarter_of_year,

  (month_of_year + 2) / 3 + 4 * (year_of_calendar - EXTRACT(YEAR FROM start_of_calendar)) AS quarter_of_calendar,

  EXTRACT(YEAR FROM cdate) AS year_of_calendar,

  CASE day_of_week
    WHEN 1 THEN 'Monday'
    WHEN 2 THEN 'Tuesday'
    WHEN 3 THEN 'Wednesday'
    WHEN 4 THEN 'Thursday'
    WHEN 5 THEN 'Friday'
    WHEN 6 THEN 'Saturday'
    WHEN 7 THEN 'Sunday'
    ELSE ''
  END AS weekday,

  /**ISO_temp: this week's thursday**/
  cDate - Day_Of_Week + 4 AS ISO_temp,

  EXTRACT (YEAR FROM ISO_temp) (FORMAT '9999') AS ISO_year,

  ((ISO_temp - ((EXTRACT(YEAR FROM ISO_temp) - 1900) * 10000 + 0101 (DATE))) / 7) + 1 (FORMAT '99')
    AS ISO_week,

  ISO_year || 'W' || ISO_week AS Week_ISO

FROM sys_calendar.caldates

REPLACE VIEW sys_calendar.ISO_calendar AS
FROM sys_calendar.calbasics_iso

Btw, in TD14 there's a new ISO calendar built-in...


29 Nov 2012

Thanks this is what was required.

guru_new 6 posts Joined 03/12
11 Sep 2013

What is the table/view name in TD14 for the ISO Calendar?

M.Saeed Khurram 544 posts Joined 09/12
12 Sep 2013

You can use
for ISO Business Calender


You must sign in to leave a comment.