Skip to Main Content

Add optional NLS_DATE_LANGUAGE parameter to NEXT_DAY()

mathguyMar 9 2017

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

Post Details
Added on Mar 9 2017