1 2 3 Previous Next 38 Replies Latest reply on Mar 10, 2017 3:54 PM by mathguy

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

    mathguy

      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

        • 1. Re: Make NEXT_DAY() independent of the invoking session's NLS_DATE_LANGUAGE

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

          And the OBVIOUS question is why you are changing your language to german if you want to use english for your functions?

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

          Why not use a third 'hack' and write  your own function to wrap Oracle's. Then you can pass that third argument and have your function:

           

          1.  alter the session to the needed setting

          2. call Oracle's function

          3. alter the sessin setting back

           

          Or take the 'slow boat to china' and submit an enhancement request to Oracle!

          • 2. Re: Make NEXT_DAY() independent of the invoking session's NLS_DATE_LANGUAGE
            mathguy

            I don't want to use English for my function, I want the query I write to run without changes (and without requiring the user to change their NLS_DATE_LANGUAGE to English before running the query) in a multi-lingual organization. I change my session's setting only for testing purposes. Imagine the same query must run across my organization, and we have offices in New Zealand, France and Germany. How do I write the query without hard-coding 'Tuesday' anywhere?

             

            Of course I could write a function; I am not sure if I can change session settings in a function (perhaps it's easy, it never crossed my mind), but I can certainly wrap one of the "first two hacks" within a function to hide it from the user.

             

            Regarding the slow boat - I imagine it is only available to paying customers. I am not one of them.

             

            Best,   mathguy

            • 3. Re: Make NEXT_DAY() independent of the invoking session's NLS_DATE_LANGUAGE
              Frank Kulash

              Hi,

               

              It's a shame that NEXT_DAY doesn't have an option for specifying the language, the way that (for example) TO_CHAR does.    If we're going to use NEXT_DAY, then, unfortunately, we must figure out what the desired day of the week is called in the current language, whatever that may be.

               

              If we want to find the next Tuesday, the methods you posted require that we first find a DATE that is a Tuesday, or a NUMBER that represents Tuesday.  It would be better (though still ugly) if we could pass 2 strings: one spelling out the desired day of the week, and the other giving the language.  That is, given a table like this:

              CREATE TABLE  table_x

              (   dt      DATE           -- 1st argument to NEXT_DAY

              ,   dy      VARCHAR2 (10)  -- desired day-of-the-week

              ,   lang    VARCHAR2 (10)  -- desired language

              ,   ans     DATE           -- correct answer (for testing)

              );

               

              INSERT INTO table_x (dt, dy, lang, ans) VALUES (DATE '2017-03-01', 'LUN.', 'French',  DATE '2017-03-06');

              INSERT INTO table_x (dt, dy, lang, ans) VALUES (DATE '2017-03-01', 'DI',   'German',  DATE '2017-03-07');

              INSERT INTO table_x (dt, dy, lang, ans) VALUES (DATE '2017-03-01', 'WED',  'English', DATE '2017-03-08');

              we'd like to call NEXT_DAY with values derived from the dt, dy and lang columns, and have it return the value in the ans column.

               

              Here's one way to do that:

              WITH    week    AS

              (

                  SELECT  SYSDATE + LEVEL  AS w_dt

                  FROM    dual

                  CONNECT BY  LEVEL <= 7

              )

              ,   got_nxt    AS

              (

                  SELECT  x.*

                  ,       NEXT_DAY ( dt

                                   , (

                                         SELECT  TO_CHAR ( w_dt

                                                         , 'DY'

                                                         )

                                         FROM    week

                                         WHERE   TO_CHAR ( w_dt

                                                         , 'DY'

                                                         , 'NLS_DATE_LANGUAGE=' || lang

                                                         ) = dy

                                     )

                                   )  AS nxt

                  FROM    table_x  x

              )

              SELECT    n.*

              ,         CASE

                            WHEN  nxt = ans

                            THEN  'OK'

                            ELSE  '***  WRONG!  ***'

                        END  AS flag

              FROM      got_nxt  n

              ORDER BY  dt, dy, lang

              ;

              TO_CHAR does allow us to specify a language.  The query above applies the power of TO_CHAR to our inputs.

              Here's the output from the query above (with NLS_DATE_LANGUAGE=ENGLISH):

              DT              DY    LANG       ANS             NXT             FLAG

              --------------- ----- ---------- --------------- --------------- -----

              01-Mar-2017 Wed DI    German     07-Mar-2017 Tue 07-Mar-2017 Tue OK

              01-Mar-2017 Wed LUN.  French     06-Mar-2017 Mon 06-Mar-2017 Mon OK

              01-Mar-2017 Wed WED   English    08-Mar-2017 Wed 08-Mar-2017 Wed OK

              You could adapt this so that

              • it didn't matter if dy was the abbreviation or the full name (as in NEXT_DAY), case-insensitive, and/or
              • lang was optional.

              I'll leave those as exercises.

              1 person found this helpful
              • 4. Re: Make NEXT_DAY() independent of the invoking session's NLS_DATE_LANGUAGE
                Paulzip

                Create a function which addresses the issue, then you can reuse and forget about it....

                 

                create or replace function next_day_nls(pDate date, pDayOfWeek varchar2, pNLSDateLang varchar2) return date is

                  vResult date;

                begin

                  begin

                    select next_day(pDate, DOW_Native)

                    into vResult

                    from (

                      select to_char(dt, 'FMDAY', NLSParam) DOW, to_char(dt, 'FMDAY') DOW_Native 

                      from (

                        select trunc(sysdate, 'iw') + level - 1 dt, 'NLS_DATE_LANGUAGE='||pNlsDateLang NLSParam

                        from dual

                        connect by level <= 7

                      )

                    )

                    where upper(substr(pDayOfWeek, 1, 3)) = substr(DOW, 1, 3);   

                  exception

                    when NO_DATA_FOUND or TOO_MANY_ROWS then

                      vResult := next_day(pDate, pDayOfWeek);  -- Try default

                  end;

                  return vResult;

                end;

                 

                alter session set nls_language = 'FRENCH';

                 

                select next_day_nls(sysdate, 'saturday', 'english') from dual

                 

                ND

                11/03/2017 18:01:20

                1 person found this helpful
                • 5. Re: Make NEXT_DAY() independent of the invoking session's NLS_DATE_LANGUAGE
                  Frank Kulash

                  Hi,

                   

                  Starting in Oracle 12, the query in reply #3 can be shortened considerably, using CROSS APPLY, like this:

                  SELECT    x.*

                  ,         n.nxt

                  ,         CASE

                                WHEN  n.nxt  = x.ans

                                THEN  'OK'

                                ELSE  '***  WRONG!  ***'

                            END  AS flag

                  FROM      table_x  x

                  CROSS APPLY  (

                                   SELECT  x.dt + LEVEL  AS nxt

                                   FROM    dual

                                   WHERE   TO_CHAR ( x.dt + LEVEL

                                                 , 'DY'

                                                 , 'NLS_DATE_LANGUAGE=' || x.lang

                                                 )  = x.dy

                                   CONNECT BY  LEVEL  <= 7

                               ) n

                  ORDER BY  dt, dy, lang

                  ;

                  1 person found this helpful
                  • 6. Re: Make NEXT_DAY() independent of the invoking session's NLS_DATE_LANGUAGE
                    Solomon Yakobson

                    NEXT_DAY takes second parameter as day name or day number, so hack#1 can be done like:

                     

                    next_day(some_date,to_number(to_char(date '1-1-1' + 5,'d')))

                     

                    where offset is 1 - 7 corresponding to Sunday - Monday. For example, next Thursday regardless of client:

                     

                    SQL> select next_day(sysdate,to_number(to_char(date '1-1-1' + 5,'d'))) from dual;

                     

                    NEXT_DAY(

                    ---------

                    09-MAR-17

                     

                    SQL> alter session set nls_territory=germany;

                     

                    Session altered.

                     

                    SQL> select next_day(sysdate,to_number(to_char(date '1-1-1' + 5,'d'))) from dual;

                     

                    NEXT_DAY

                    --------

                    09.03.17

                     

                    SQL>

                     

                    SY.

                    1 person found this helpful
                    • 7. Re: Make NEXT_DAY() independent of the invoking session's NLS_DATE_LANGUAGE
                      mathguy

                      NEXT_DAY takes second parameter as day name or day number [...]

                       

                      Interesting. I will file this under "undocumented overrides overloads". The documentation, to which I provided a link in my original post, does not mention it.

                       

                      Other than that, the solution does pretty much the same thing as my "first hack", it just provides a number for the day of the week instead of a name in a specific language. There may be some benefits to this (perhaps less computation, checking system tables for day names in various languages). Not too happy about having one more function call (explicit as you did, or implicit if we don't use it) to convert from string to number.

                       

                      I would rather use this with my "second hack" - having to know beforehand what day of the week was '1-1-1' is not too appealing.

                       

                      Thank you!    mathguy

                      • 8. Re: Make NEXT_DAY() independent of the invoking session's NLS_DATE_LANGUAGE

                        mathguy wrote:

                         

                        NEXT_DAY takes second parameter as day name or day number [...]

                         

                        Interesting. I will file this under "undocumented overrides". The documentation, to which I provided a link in my original post, does not mention it.

                         

                        Well - maybe not so 'interesting' since 'day number' brings its own set of issues since it (and other format variables) is dependent on the NLS_TERRITORY parameter:

                         

                        https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_commands_1029.htm#OLADM780

                        The datetime format element D returns the number of the day of the week (1-7). The day of the week that is numbered 1 is specified implicitly by the initialization parameter NLS_TERRITORY.

                        So day 1 may be Sunday in some territories but 7 in others where 1 is Monday.

                        • 9. Re: Make NEXT_DAY() independent of the invoking session's NLS_DATE_LANGUAGE
                          Solomon Yakobson

                          Reread my reply. Result is NLS independent. If client is in the US then to_char(sysdate + 5,'d') will return 5 and, since US week starts Sunday, 5 means Thursday. If client is in Romania or Lithuania, then to_char(sysdate + 5,'d') will return 4 and, since week there starts Monday, 4 again means Thursday.  If client is in Oman, then to_char(sysdate + 5,'d') will return 6 and, since week there starts Saturday, 6 again means Thursday.

                           

                          SY.

                          • 10. Re: Make NEXT_DAY() independent of the invoking session's NLS_DATE_LANGUAGE
                            mathguy

                            I can't speak for RP, but I don't think he was criticizing your solution; I think he was just pointing out, correctly, that numeric "day of the week" is dependent on NLS_TERRITORY, so in a solution (like yours) you have to account for that. As you did. No doubt the solution you provided is correct, it just has to work around this difficulty.

                            • 11. Re: Make NEXT_DAY() independent of the invoking session's NLS_DATE_LANGUAGE
                              Mike Kutz

                              I wonder, could one expand on your solution by creating a package of "enumerated values"??

                               

                              create or replace
                              package day_enum
                              authid current_user
                              as
                              function tuesday return int;
                              ...
                              end;
                              /
                              create or replace
                              package body day_enum
                              as
                              function tuesday return int
                              as
                              begin
                                return to_number(  to_char( to_date( '3-jan-2017', 'dd-mon-yyyy'), 'd' ) );
                              end;
                              ....
                              end;
                              /
                              

                               

                              Then, the end users would only need to do the following:

                              select next_day( sysdate, DAY_ENUM.TUESDAY() ) from dual
                              

                               

                               

                              MK

                              • 13. Re: Make NEXT_DAY() independent of the invoking session's NLS_DATE_LANGUAGE

                                Reread my reply. Result is NLS independent. If client is in the US then to_char(sysdate + 5,'d') will return 5 and, since US week starts Sunday, 5 means Thursday. If client is in Romania or Lithuania, then to_char(sysdate + 5,'d') will return 4 and, since week there starts Monday, 4 again means Thursday. If client is in Oman, then to_char(sysdate + 5,'d') will return 6 and, since week there starts Saturday, 6 again means Thursday.

                                 

                                You are correct.

                                 

                                The result is NLS independent because NLS_TERRITORY is used two times. It is used once to determine the day number and then used again when that day number is used in the NEXT_DAY method.

                                 

                                The solution you posted relies on NEXT_DAY accepting a day of week number. Couldn't that be simplified by removing the TO_NUMBER and using 'day' in the format instead of 'd'?

                                select next_day(sysdate,to_char(date '1-1-1' + 5,'day')) from dual;

                                • 14. Re: Make NEXT_DAY() independent of the invoking session's NLS_DATE_LANGUAGE

                                  I can't speak for RP, but I don't think he was criticizing your solution;

                                  I can speak for him and he was wrong!

                                   

                                   

                                  I think he was just pointing out, correctly, that numeric "day of the week" is dependent on NLS_TERRITORY, so in a solution (like yours) you have to account for that.

                                  He accounted for it by using NLS_TERRITORY twice (see my reply to him) - once in the TO_CHAR used to extract the 'day number' based on the dummy date literal '1-1-1'. Then again in the NEXT_DAY  where the day number has already been adjusted for the territory.

                                   

                                  That dummy date literal '1-1-1' could be ANY literal. It just needs to be a value that allows the +5 to map to Thursday (assuming you want that mapping - 1 - Sunday, 2 - Monday, etc

                                   

                                  This works.

                                  select next_day(sysdate,to_char(date '1967-3-25' + 5,'day')) from dual;

                                  Just pick any date at all and then adjust it until the +5 gives you a thursday:

                                  select to_char(date '1972-8-17' + 5,'day') from dual;

                                  That gives a Tuesday so it would map '5' to Tuesday. So just adjust the date by 2 days

                                  select to_char(date '1972-8-19' + 5,'day') from dual;

                                  Now you get a Thursday so the mapping is correct. Repeat Solomon's adjusted query

                                  select next_day(sysdate,to_char(date '1972-8-19' + 5,'day')) from dual;

                                  . . .

                                  09.03.17

                                  The '1-1-1' just happens to work so is a more convenient value.

                                   

                                  So eliminating the undocumented need for the TO_NUMBER works also

                                  select next_day(sysdate,to_char(date '1972-8-19' + 5,'day')) from dual;

                                  . . .

                                  09.03.17

                                  That avoids the  problem you mentioned about the 'day' being different in different languages

                                  The "day of the week" argument must be in the NLS_DATE_LANGUAGE of the session in which the function is invoked.

                                  You just use Solomon's technique but extract the textual name of the day instead of the number so that you are using the documented API of the NEXT_DAY function.

                                  1 2 3 Previous Next