Archives of the TeradataForum
Message Posted: Mon, 22 Aug 2005 @ 17:48:23 GMT
> 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 ...
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|