14 thoughts on “how to get monday=1, tuesday=2…

  1. Eugene

    Laurent,
    I know it is not about your post, but wonder if you can help me.
    How can I determine first business day of the month passing in sysdate?
    Thanks,
    Eugene

  2. Laurent Schneider Post author

    select to_char(trunc(sysdate,’mm’)+decode(TO_CHAR(TRUNC(sysdate,’MM’),’DY’,’NLS_DATE_LANGUAGE=AMERICAN’),’SAT’,2,’SUN’,1,0),’DL’) from dual;
    TO_CHAR(TRUNC(SYSDATE,’MM’)+D
    —————————–
    Friday, February 01, 2008

  3. Eugene

    Laurent,
    Thanks for reply, but …
    SQL> select to_char(trunc(sysdate,’mm’) + decode(TO_CHAR(TRUNC(sysdate,’MM’),’DY’,’NLS_DATE_LANGUAGE
    =AMERICAN’),’SAT’,2,’SUN’,1,0),’DL’) from dual;
    select to_char(trunc(sysdate,’mm’) + decode(TO_CHAR(TRUNC(sysdate,’MM’),’DY’,’NLS_DATE_LANGUAGE=AMER

    ERROR at line 1:
    ORA-01821: date format not recognized

    Eugene

  4. Laurent Schneider Post author

    try to upgrade to a supported release of Oracle 8)

    the formatting is not necessary

    select trunc(sysdate,’mm’)+decode(TO_CHAR(TRUNC(sysdate,’mm’),’DY’,’NLS_DATE_LANGUAGE=AMERICAN’),’SAT’,2,’SUN’,1,0) from dual;

  5. Gary

    Interestingly, there’s a difference in the way “numeric date language” and the “D” format handle territory.

    > alter session set nls_territory=’America’;

    Session altered.

    > select to_char(sysdate,’DAY’,’NLS_DATE_LANGUAGE=”numeric date language”’),
    2 to_char(sysdate,’DAY D’) from dual;
    T TO_CHAR(SYS
    – ———–
    4 THURSDAY 5

    > alter session set nls_territory=’Australia’;

    Session altered.

    > select to_char(sysdate,’DAY’,’NLS_DATE_LANGUAGE=”numeric date language”’),
    2 to_char(sysdate,’DAY D’) from dual;
    T TO_CHAR(SYS
    – ———–
    4 THURSDAY 4

  6. Brian Tkatch

    For the month passing:

    SELECT
    CASE
    WHEN TO_CHAR(A, ‘D’) IN (7, 1) THEN NEXT_DAY(A, 2)
    ELSE A
    END
    FROM
    (SELECT TRUNC(ADD_MONTHS(SysDate, +1), ‘MM’) A FROM Dual);

    Of course, the country’s specifics require manual intervention. 🙂

  7. Laurent Schneider Post author

    this is all the point about using the post… Do not use ‘D’ !

    Unfortunately you have no NLS_DATE_TERRITORY, therefore you cannot do something like

    select to_char(sysdate,’D’, ‘nls_date_territory=switzerland’), to_char(sysdate,’DL’,’nls_date_territory=america’) from dual;

    it is simply impossible. But be patient, I have raised an enhancement request 4 years ago
    bug 3769415

  8. Brian Tkatch

    Ah, now i see what you did. *slaps self on head*

    I could change the D to Dy, but its jsut a tradeoff of the more clear choice of using CASE/DECODE to choose the day, or to add the required amount of days. Personally, i think NEXT_DAY is a lot quite clearer.

  9. Laurent Schneider Post author

    next_day is also session dependent. having a session dependent statement is way more uncomfortable.
    1) you cannot use it in materialized view
    2) you cannot use it in function based index
    3) you cannot have different behavior within the same statement, like displaying the default local currency, the American day of week, and the swiss short time TS (I am quite busy those days while writing about it in my book

    About Dy, just to make you worry, it may changed as in changed in French from 9.2.0.8 to 10.1.0.5 from 2 chars to 4 chars abbreviation (imagine the hassle if it were hard coded in your program!) 😈

    Well, I never use NEXT_DAY as a large number of my customers do have different locale settings. However I admit that if you never used and will never anything than american_america, then why bother, go for NEXT_DAY 😎

  10. Pingback: Use SQL to Create a Calendar in Mutliple Languages » Eddie Awad's Blog

  11. Pingback: Use SQL to Create a Calendar in Mutliple Languages « oracle fusion identity

  12. Pingback: Use SQL to Create a Calendar in Mutliple Languages | Oracle Administrators Blog - by Aman Sood

Comments are closed.