Archives of the TeradataForum

Message Posted: Thu, 30 Nov 2006 @ 13:31:27 GMT

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

There is indeed one easy-on-the-eye way if you can afford using ODBC:

     sel WEEK(CURRENT_DATE);

WEEK is an ODBC function, so don't try the syntax in BTEQ, FastLoad etc.

However, the main issue with week numbers is not how easy it is to assign them but whether these numbers fit into the week numbering scheme employed by your company. Dieter has already mentioned the ISO standard, which the OP used as an example (if I may comment on it, the example was not entirely consistent as it used ISO for the WEEK but did not use ISO definition for the YEAR, and ISO year is not always the same as the calendar year.)

It is important to check with your customers what exactly they understand under "week number". It may be ISO, it may be something totally different. And if the definition is "customized" you might consider having a reference table with your company's calendar. I would advise, though, to avoid it as the presence of such a table will always imply an extra join. A nice compromise could be having such a customized calendar and a UDF calculating the right (right for your company) numbers on the fly so that users who don't like too many joins can use the UDF. If the use of UDFs is acceptable at your site at all, of course.

All this fuss with week numbers is only worth considering if the numbers are going to be used in such things as financial reports. A day or two more or less in a financial year can make a few percent difference. It is better to sort this out before the day you have to run the end-of-year reports.

Regards,

Victor

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