Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Make NEXT_DAY() independent of the invoking session's NLS_DATE_LANGUAGE

mathguyMar 6 2017 — edited Mar 10 2017

The function NEXT_DAY() takes two arguments. The first argument is a date and the second is a string expression, the name of a "day of the week". For example, if the second argument is 'Tuesday', the function will return the earliest date that is a Tuesday and is strictly later than the first argument. The "day of the week" argument must be in the NLS_DATE_LANGUAGE of the session in which the function is invoked. So, for example, if I ALTER SESSION to set the NLS_DATE_LANGUAGE to 'German' or 'French', I will get an error message if I use NEXT_DAY(..., 'Tuesday').

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

Note (irrelevant for this discussion): only the first three letters in the day name are relevant, the rest is ignored so it can be garbage; I could use 'Tuemathguy' and the function would work OK in English. "Three" letters is for English; whatever the correct number of letters in the abbreviation of day-of-the-week names in the NLS_DATE_LANGUAGE is the relevant number of letters for the NEXT_DAY() function.

Unfortunately, unlike many other functions that have to do with dates, NEXT_DAY() does not take an argument for NLS_DATE_LANGUAGE. So a query that uses NEXT_DAY() will require a hack, if it is meant to be run in different locations and we don't want to ask the invoker to alter their session to set their NLS_DATE_LANGUAGE to a fixed value, such as 'English'.

I can think of two such hacks, but I don't like them. Question: Is there a better way? Of course, it would be best if Oracle would allow a third argument for nlsparam, as they do for other functions...

The hacks are: (1) we can take a known date for the desired day of the week, and use   to_char(that_date, 'Day')    as the second argument to NEXT_DAY();  (2) similarly, instead of using a hard-coded date that is known to be a certain day of the week, we can instead truncate SYSDATE to a Monday, using the 'iw' format model, and then add whatever number we need to get the desired day of the week. Both hacks use the fact that TO_CHAR() returns the name of the day in the NLS_DATE_LANGUAGE of the invoking session. Often we want to override that by specifying NLS_DATE_LANGUAGE explicitly in the function call, but in this case we want just the opposite.

In the illustrations below, I want to find the first Tuesday in August 2017. (Remember, that means I must apply NEXT_DAY() to the date preceding 2017-08-01, because NEXT_DAY() returns a date strictly greater than the first argument. To indicate that very clearly, I will not write 2017-07-31, but I will instead subtract 1 from 2017-08-01.)

SQL> alter session set nls_date_format = 'yyyy-mm-dd';
SQL> alter session set nls_date_language = 'English';

SQL> select next_day(date '2017-08-01' - 1, 'Tuesday') as next_tue from dual;

NEXT_TUE
----------
2017-08-01

OK, so far we know that 2017-08-01 will be a Tuesday. We can and will use this later. (Any other date that is known beforehand to be a Tuesday would work just as well.)

Now let's change the session's date language to German and try to run the same query. It should fail, and it does.

SQL> alter session set nls_date_language = 'German';

SQL> select next_day(date '2017-08-01' - 1, 'Tuesday') as next_tue from dual;
select next_day(date '2017-08-01' - 1, 'Tuesday') as next_tue from dual

                                       *
ERROR at line 1:
ORA-01846: Kein gültiger Wochentag

OK, so let's use the date we know to be a Tuesday, and see what they call it in German. We can use that in our query (but, again, it will be hard-coded, just in a different language - German instead of English).

SQL> select to_char(date '2017-08-01', 'Day') from dual;

TO_CHAR(DA
----------
Dienstag

SQL> select next_day(date '2017-08-01' - 1, 'Dienstag') as next_tue from dual;

NEXT_TUE
----------
2017-08-01

Alright, now let's try the first hack. The query below is independent of the session NLS_DATE_LANGUAGE, but it hard-codes a date known to be a Tuesday. Still not very satisfying.

SQL> select next_day(date '2017-08-01' - 1, to_char(date '2017-08-01', 'Day')) as next_tue from dual;

NEXT_TUE
----------
2017-08-01

Let's test the second hack. First we truncate any date to a Monday, using the 'iw' format model; we add whatever number (1 in this case) to get a Tuesday, and we use TO_CHAR() to get the name of Tuesday in German. Then we pass this value to NEXT_DAY() to make the query independent of NLS_DATE_LANGUAGE. This seems better, as it doesn't require advance knowledge of anything - but it's a lot of work for something that should be much easier. Certainly, if we needed to do this on many rows in a query, we would compute the translation of "Tuesday" in a subquery so we wouldn't have to perform the same computation once for every input row.

SQL> select next_day(date '2017-08-01' - 1, to_char(trunc(sysdate, 'iw') + 1, 'Day')) as next_tue from dual;

NEXT_TUE
----------
2017-08-01

Let's change the NLS_DATE_LANGUAGE to French and test a little more.

SQL> alter session set nls_date_language = 'French';

SQL> select to_char(trunc(sysdate, 'iw') + 1, 'Day') as french_tue from dual;

FRENCH_TUE
----------
Mardi

SQL> select next_day(date '2017-08-01' - 1, to_char(trunc(sysdate, 'iw') + 1, 'Day')) as next_tue from dual;

NEXT_TUE
----------
2017-08-01

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 7 2017
Added on Mar 6 2017
38 comments
7,856 views