Forum Stats

  • 3,837,046 Users
  • 2,262,222 Discussions
  • 7,900,189 Comments

Discussions

Add optional NLS_DATE_LANGUAGE parameter to NEXT_DAY()

mathguy
mathguy Member Posts: 10,568 Blue Diamond
edited Mar 9, 2017 10:32AM in Database Ideas - Ideas

Unlike other date functions, NEXT_DAY() does not allow the specification of nls values to make its use independent of the invoking session's parameters. This has been discussed to a good extent in a thread in the SQL and PL/SQL community, so I won't repeat it here: Make NEXT_DAY() independent of the invoking session's NLS_DATE_LANGUAGE

Moreover, NEXT_DAY() has an undocumented(?) option to pass in a day number as the second argument, instead of the name of a day of the week. That, too, depends on the invoking session's parameters, in this case NLS_TERRITORY (since the number is the "number of the day in the week" according to local convention, it is not the ISO day of the week). So an option to pass in an explicit NLS_TERRITORY value may also be helpful. (So would be documenting this feature in the first place, if its omission was in error and not deliberate.)

mathguyMike KutzCarlosDLGKalpataruNiels HeckerSven W.ctriebuser12175034wweickerBrunoVromanFrank KulashcormacoGregVKayKAndrewSayergaverillberxThorsten KettnerDanilo Piazzalunga2763917user-dym_on_3270226fac586William RobertsonMarwimsdstuberMarkStewart
26 votes

Active · Last Updated

Comments

  • +1 - the entire set of functions should be standardized so they work consistently.

    Thorsten Kettner
  • Sven W.
    Sven W. Member Posts: 10,537 Gold Crown

    Nls param is missing. However the other functions like to_char or to-date also do not support all the nls_settings. For example nls_territory is missing and this is one that would be needed for NEXT_DAY.

    I agree the docs are misleading. Although one could argue, that "day of the week" means '1' to '7'.

    https://docs.oracle.com/database/121/SQLRF/functions118.htm#SQLRF00672

    PurposeNEXT_DAY returns the date of the first weekday named by <span class="codeinlineitalic" style="font-style: italic;">char</span> that is later than the date <span class="codeinlineitalic" style="font-style: italic;">date</span>. The return type is always DATE, regardless of the data type of <span class="codeinlineitalic" style="font-style: italic;">date</span>. The argument <span class="codeinlineitalic" style="font-style: italic;">char</span> must be a day of the week in the date language of your session, either the full name or the abbreviation. The minimum number of letters required is the number of letters in the abbreviated version. Any characters immediately following the valid abbreviation are ignored. The return value has the same hours, minutes, and seconds component as the argument <span class="codeinlineitalic" style="font-style: italic;">date</span>.

    Format models: https://docs.oracle.com/database/121/SQLRF/sql_elements004.htm#SQLRF00212

    D 

    Yes

    Day of week (1-7). This element depends on the NLS territory of the session.

    DAY

    Yes

    Name of day.

    DD 

    Yes

    Day of month (1-31).

    DDD 

    Yes

    Day of year (1-366).

    ...
    DY 

    Yes

    Abbreviated name of day.

    I added some feedback to the documentation page.

  • User_5IN9J
    User_5IN9J Member Posts: 1 Green Ribbon

    I think thi is indipendent of territory:

    SELECT TRUNC(NEXT_DAY(SYSDATE, TO_CHAR(TRUNC(SYSDATE, 'IW'), 'DAY'))) FROM dual; -- next monday

    SELECT TRUNC(NEXT_DAY(SYSDATE, TO_CHAR(TRUNC(SYSDATE, 'IW') + 1, 'DAY'))) FROM dual; -- next tuesday

    SELECT TRUNC(NEXT_DAY(SYSDATE, TO_CHAR(TRUNC(SYSDATE, 'IW') + 2, 'DAY'))) FROM dual; -- next wednesday

    is it?

  • mathguy
    mathguy Member Posts: 10,568 Blue Diamond

    NEXT_DAY doesn't truncate the time-of-day to midnight; so you shouldn't truncate in your formula, either.

    Other than that, the computation is correct - but that's not the point. There are other ways to get the same result. The request is to have an nls_date_language parameter so that we don't have to use such formulas in our code. Since that is already supported for almost all other date functions, this addition can't be too complex for Oracle.