Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 63 Insurance
- 536.4K On-Premises Infrastructure
- 138.3K Analytics Software
- 38.6K Application Development Software
- 5.8K Cloud Platform
- 109.5K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.3K Integration
- 41.6K Security Software
Add optional NLS_DATE_LANGUAGE parameter to NEXT_DAY()
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.)
Comments
-
+1 - the entire set of functions should be standardized so they work consistently.
-
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
Purpose
NEXT_DAYreturns 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 alwaysDATE, 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.
DAYYes
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.
-
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?
-
NEXT_DAYdoesn'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_languageparameter 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.