Archives of the TeradataForum

Message Posted: Thu, 31 Jul 2003 @ 20:44:36 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

It doesn't look like a solution "without using a lookup table (like Sys_Calendar)" ;-)

And Sys_calendar.week_of_year is *not* the ISO week. According to ISO weeks start at monday and the first week of a year is the week with the first thursday in it. That's why Jan 1 maybe in week 1 of the current year or the last week of the year before.

I wrote a modified sys_calendar with ISO data and it was quite hard to implement an algorithm in SQL to calculate ISO weeks (still looking for a better one).

SELECT
   cdate,

   -- day_of_week
   ((cdate - DATE '0001-01-01') mod 7) + 1 AS Day_Of_Week

   -- ISO_temp: Thursday within a ISO week, used to calculate ISO weeks
   ,cDate - Day_Of_Week + 4 as ISO_temp

   -- ISO_Year
   ,EXTRACT (YEAR FROM ISO_temp) (FORMAT '9999') AS ISO_Year

   -- ISO_Week: Julian day / 7 + 1
   ,((ISO_temp - (ISO_temp / 10000 * 10000 + 101 (date))) / 7) + 1
(FORMAT '99') AS ISO_Week

   -- ISO_Week_of_year
   ,ISO_Year || 'W' || ISO_Week AS ISO_Week_of_year
FROM sys_calendar.Caldates
where cdate between '2002-12-28' and '2003-01-20'
order by cdate;

You could create a single SQL expression, but it's quite ugly. And it *is* probably the best to use a calendar table, because the calculation has to be done on every row and you can't use an index on a calculated column.

Or maybe it's better to calcualte the startdate of a ISO week instead, so you can use

Date_col = > Statement_to_calculate_first_day_of_ISO_week_xxx

Dieter

	<Prev	Next>		<<First	<Prev	Next>	Last>>