ISO 8601 Calendar View for Teradata

Here’s a little snippet that I thought someone might find useful. At my previous job, we began exporting our sales data to a 3rd party consultant firm. They required our data to be on the ISO 8601 calendar. At my dismay, I could not find any pre-built calendars for this purpose so I ended up writing this view.

This view converts Teradata’s sys_calendar to an ISO 8601:2000 calendar. Feel free to use as needed and if you have a more efficient way of doing this, please comment below.

Requirements

  1. Weeks begin on a Monday and end on a Sunday.
  2. The first week of the ISO year always contains January 4th.
  3. The remaining calculations are calculated from the January 4th logic.
  4. Periods and Quarters are using 4-4-5 pattern (4-4-6 in leap year).
CREATE VIEW CALENDAR.ISO_CALENDAR AS


SELECT 	calendar_date AS CAL_DATE,
		calendar_date - 364 AS CAL_DATE_LAST_YEAR,
		ISO_YEAR AS ISO_YEAR,
		CASE WHEN iso_wk_of_year IN (1,2,3,4) THEN 1
			 WHEN iso_wk_of_year IN (5,6,7,8) THEN 2
			 WHEN iso_wk_of_year IN (9,10,11,12,13) THEN 3
			 WHEN iso_wk_of_year IN (14,15,16,17) THEN 4
			 WHEN iso_wk_of_year IN (18,19,20,21) THEN 5
			 WHEN iso_wk_of_year IN (22,23,24,25,26) THEN 6
			 WHEN iso_wk_of_year IN (27,28,29,30) THEN 7
			 WHEN iso_wk_of_year IN (31,32,33,34) THEN 8
			 WHEN iso_wk_of_year IN (35,36,37,38,39) THEN 9
			 WHEN iso_wk_of_year IN (40,41,42,43) THEN 10
			 WHEN iso_wk_of_year IN (44,45,46,47) THEN 11
			 WHEN iso_wk_of_year IN (48,49,50,51,52,53) THEN 12
		END AS ISO_PERIOD,
		CASE WHEN iso_wk_of_year IN (1,2,3,4,5,6,7,8,9,10,11,12,13) THEN 1
			 WHEN iso_wk_of_year IN (14,15,16,17,18,19,20,21,22,23,24,25,26) THEN 2
			 WHEN iso_wk_of_year IN (27,28,29,30,31,32,33,34,35,36,37,38,39) THEN 3
			 WHEN iso_wk_of_year IN (40,41,42,43,44,45,46,47,48,49,50,51,52,53) THEN 4
		END AS ISO_QUARTER,
		ISO_DAY_OF_YEAR AS ISO_DAY_OF_YEAR,
		ISO_WK_OF_YEAR AS ISO_WK_OF_YEAR,
		ISO_DAY_OF_WEEK AS ISO_DAY_OF_WEEK,
		ISO_WK_BEGIN_DATE AS ISO_WK_BEGIN_DATE,
		ISO_WK_END_DATE AS ISO_WK_END_DATE
FROM
		(
			SELECT  c.calendar_date,
					c.calendar_date - iso.iso_yr_begin_date + 1 AS iso_day_of_year,
					c.calendar_date - CASE WHEN c.day_of_week = 1
					THEN 6 ELSE (c.day_of_week - 2)
					END AS iso_wk_begin_date,
					c.calendar_date - iso_wk_begin_date + 1 AS iso_day_of_week,
					iso_wk_begin_date + 6 AS iso_wk_end_date,
					((c.calendar_date - iso.iso_yr_begin_date)
					- (c.calendar_date - iso_wk_begin_date)) / 7 + 1 AS iso_wk_of_year,
					iso.iso_year
			FROM 	sys_calendar.calendar c
					INNER JOIN
					(
					SELECT  iso_yr_begin_date,
							MIN(iso_yr_begin_date) OVER (ORDER BY iso_yr_begin_date ROWS
							BETWEEN 1 FOLLOWING AND 1 FOLLOWING) - 1 AS iso_yr_end_date,
							iso_year
					FROM
							(
								SELECT calendar_date - CASE WHEN day_of_week = 1
															THEN 6
															ELSE (day_of_week - 2)
															END AS iso_yr_begin_date,
										iso_yr_begin_date + 6 AS iso_wk_one_end_date,
										EXTRACT(year FROM iso_wk_one_end_date) AS iso_year
								FROM sys_calendar.calendar
								WHERE calendar_date >= '2000-01-01'
								AND calendar_date <= '2030-12-31'
								AND day_of_year = 4 -- First week of ISO year always contains January 4th.
							) d
					) iso ON c.calendar_date BETWEEN iso.iso_yr_begin_date AND iso.iso_yr_end_date
		) dtl
;

Leave a Reply

Your email address will not be published. Required fields are marked *