Home Page for the TeradataForum

Archives of the TeradataForum

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

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

Subj:   Re: DAYOFWEEK Function and BTEQ
From:   Victor Sokovin

  maybe this is an alternative option for you:  

          > 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



  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020