- 384.5K All Categories
- 2.5K Data
- 551 Big Data Appliance
- 1.9K Data Science
- 451.3K Databases
- 222.1K General Database Discussions
- 32 Multilingual Engine
- 560 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 561 SQLcl
- 4K SQL Developer Data Modeler
- 187.4K SQL & PL/SQL
- 21.4K SQL Developer
- 296.8K Development
- Application Development
- 18 Developer Projects
- 140 Programming Languages
- 293.5K Development Tools
- 112 DevOps
- 3.1K QA/Testing
- 646.2K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 193 Java Community Process
- 106 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 21 Java Essentials
- 165 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 206 Java User Groups
- 525 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 178 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 236 Portuguese
Make NEXT_DAY() independent of the invoking session's NLS_DATE_LANGUAGE
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').
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
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
DienstagSQL> select next_day(date '2017-08-01' - 1, 'Dienstag') as next_tue from dual;NEXT_TUE
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
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
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
MardiSQL> select next_day(date '2017-08-01' - 1, to_char(trunc(sysdate, 'iw') + 1, 'Day')) as next_tue from dual;NEXT_TUE