Archives of the TeradataForum

Message Posted: Mon, 22 Aug 2005 @ 17:48:23 GMT

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

          > sel (date - date'0001-01-01') mod 7 + 1;

          > 1 = Monday
          > 2 = Tuesday
          > 3 = Wednesday
          >   and so on...

This seems to be obvious but the formula should not actually work like this. Gregorian calendar only started in 1582 and only in a few countries. Countries which joined first had to "skip" 10 days. Other territories joined later and some of them had to skip more days but that's aside.

If your formula really works it means that Teradata does not take into account the "missing days". I can't find any reference for this in SQL Reference manuals. Is it documented elsewhere?

Oracle seems to have implemented this differently.

Using something like

     SELECT TO_CHAR(TO_DATE('1582-10-NN','YYYY-MM-DD'), 'DY')
         FROM dual;

on a system with the US NLS I get

     ...
     1582-10-03 : WED
     1582-10-04 : THU (the last day of the Julian calendar)
     1582-10-05 : FRI (day skipped)
     1582-10-06 : FRI (day skipped)
     1582-10-07 : FRI (day skipped)
     ...
     1582-10-14 : FRI (day skipped - yeah, that's quite a few Fridays in a row)
     1582-10-15 : FRI (the first day of the Gregorian calendar)
     1582-10-16 : SAT
     1582-10-15 : SUN
     ...

Regards,

Victor

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