For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
I am looking to bring the select statement sql which I am running in the database to AWR report Is there any way to pin a sql to awr report
+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
PurposeNEXT_DAY returns the date of the first weekday named by char that is later than the date date. The return type is always DATE, regardless of the data type of date. The argument char 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 date.
Purpose
NEXT_DAY returns the date of the first weekday named by char that is later than the date date. The return type is always DATE, regardless of the data type of date. The argument char 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 date.
NEXT_DAY
char
date
DATE
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
Name of day.
DD
Day of month (1-31).
DDD
Day of year (1-366).
...
DY
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_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.
nls_date_language