Forum Stats

  • 3,728,472 Users
  • 2,245,631 Discussions
  • 7,853,549 Comments

Discussions

Add ISO day to format model for date functions

Thorsten Kettner
Thorsten Kettner Member Posts: 31 Red Ribbon
edited February 2019 in Database Ideas - Ideas

In order to have a reliable day number, I suggest adding the format 'ID' to the format models to represent the ISO day (according to ISO 8601), where 1 is always Monday and 7 is always Sunday.

SELECT * FROM surcharges WHERE isodaynum = TO_CHAR(SYSDATE, 'ID');

Explanation:

So far there only exists a single D for format masks, meaning "day of week (1-7)". Which weekdays the numbers 1-7 stand for, however, depends on the session's NLS_TERRITORY setting. So while we can use

SELECT 'This is the ' || TO_CHAR(SYSDATE, 'D') || '. day of the week.' FROM DUAL;

We cannot use

SELECT * FROM surcharges WHERE daynum = TO_CHAR(SYSDATE, 'D');

because the result would be non-deterministic. It would return either the Sunday or the Monday surcharges depending on current session settings.

Thorsten KettnerSven W.William Robertsonfac586
4 votes

Active · Last Updated

Comments

  • Sven W.
    Sven W. Member Posts: 10,507 Gold Crown
    edited February 2019

    I support that idea.

    Although to be fair we can currently use the IW format mask for most such tasks.

    For example to harmonize all days to an iso week we can do this.

    select trunc(sysdate,'IW') from dual;


    And to find out what day of week it is according to iso-numbering (Monday= day 1) then we have several other possiblities.

    select trunc(sysdate) - trunc(sysdate,'IW') + 1 from dual;

    or by using a specific day, that we know to be monday

    select to_number(to_char(sysdate,'D')) - (to_number(to_char(date '2001-01-01','D')) - 1) weekday_corrected from dual;

    What doesn't work is to use NLS_TERRITORY

    select to_char(date '2001-01-01','D','nls_territory=GERMANY') from dual

    ORA-12702: invalid NLS parameter string used in SQL function

    Thorsten Kettner
Sign In or Register to comment.