June 27, 2007

German Weekday

The big problem of the date-conversion "to_char (sysdate, 'D')" is, that depending on the NLS you get different results:

Sunday is the first day in the US
Monday is the first day in Germany

For the Green-Thursday of 2000 you get:

Green_Thursday := to_date ('23.03.2000', 'DD.MM.YYYY');
America: to_char (Green_Thursday, 'D') = 5
Germany: to_char (Green_Thursday, 'D') = 4

this is sub-optimal, because it's a format-mask, which is based on the underlying NLS and not on a strict rule.

My solution in this case is: The function German_Weekday

FUNCTION German_Weekday (P_Date IN DATE)
RETURN NUMBER IS
  V_Delta NUMBER;
BEGIN
  -- Reference-Day: Green Thursday 2000 = Day 4 in Germany
  V_Delta := TO_NUMBER (TO_CHAR (TO_DATE ('23.03.2000',
                                          'DD.MM.YYYY'),
                                 'D')) - 4;
  RETURN (TO_NUMBER (TO_CHAR (P_Date-V_Delta, 'D')));
END;

and American_Weekday

FUNCTION American_Weekday (P_Date IN DATE)
RETURN NUMBER IS
  V_Delta NUMBER;
BEGIN
  -- Reference-Day: Green Thursday 2000 = Day 5 in the US
  V_Delta := TO_NUMBER (TO_CHAR (TO_DATE ('23.03.2000',
                                          'DD.MM.YYYY'), 
                                 'D')) - 5;
  RETURN (TO_NUMBER (TO_CHAR (P_Date-V_Delta, 'D')));
END;

this function always returns the correct german 'D'-formatmask, indepent of the NLS.

have fun and use it
Gerd

6 comments:

  1. AnonymousJuly 01, 2007

    Your functions won't return the correct result for some other territory settings.
    Take for example MOROCCO:

    SQL> alter session set nls_territory='MOROCCO';

    Session altered.

    SQL> select to_char(to_date ('23.03.2000', 'DD.MM.YYYY'), 'D') from dual;

    T
    -
    6

    ReplyDelete
  2. Gerd VolbergJuly 02, 2007

    Thanks Jens, you found a problem! The new code now works with a delta-day, which solves this problem.

    ReplyDelete
  3. AnonymousAugust 21, 2007

    select mod(to_number(to_char(sysdate,'J')),7) from dual

    each day always has the same number, anywhere.

    ReplyDelete
  4. Gerd VolbergAugust 22, 2007

    The julian format gives you a constant value back. The tuesday is 1, wednesday = 2, ...
    After this conversion you have to use an offset to get the correct german or american format.
    The select produces a database I/O, which is not so fast as the function.

    ReplyDelete
  5. Laurent SchneiderSeptember 04, 2007

    To have monday=1, I use trunc(d)-trunc(d,'iw')+1

    ex:
    select
    trunc(sysdate)-trunc(sysdate,'iw')+1,
    to_char(sysdate,'Day')
    from dual;
    2 Tuesday

    which 100% nls independant...

    ReplyDelete
  6. AnonymousSeptember 30, 2008

    Thanks Laurent, just what I needed.

    ReplyDelete