how to get monday=1, tuesday=2…

I wrote it already :
Laurent Schneider » Blog Archive » TO_CHAR(…, ‘D’)

I thought the one I found out this night was fun
select to_char(sysdate,'DAY',
'NLS_DATE_LANGUAGE=''numeric date language''')
from dual;

[edit]this feature is not documented, do not use in prod[/edit]


  • 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?

  • 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;
    Friday, February 01, 2008

  • 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


  • 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;

  • 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;
    – ———–
    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;
    – ———–
    4 THURSDAY 4

  • For the month passing:

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

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

  • 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

  • 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.

  • 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 to 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 😎

  • […] Laurent Schneider […]

  • Use SQL to Create a Calendar in Mutliple Languages | Oracle Administrators Blog - by Aman Sood says:

    […] Laurent Schneider […]

Leave a Reply

Your email address will not be published.