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

Mike Kutz · Mar 7, 2017 6:29PM

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

create or replacepackage day_enumauthid current_userasfunction tuesday return int;...end;/create or replacepackage body day_enumasfunction tuesday return intasbegin  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

Mike Kutz · Mar 7, 2017 6:34PM

mathguy

Make sure you create a post in

MK

Unknown · Mar 7, 2017 6:47PM

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;

Unknown · Mar 7, 2017 7:05PM

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.

sdstuber · Mar 7, 2017 8:06PM

What about setting 7 context values to do the translations for you?
You might consider this using a magic value, but there is no math predicated on a known Date/Day-of-Week. The setup only assumes that there are 7 days in a week and then iterates getting whatever value they happen to be. You could use sysdate instead of a fixed date if you wanted.

If you wanted to support a dynamic nls_territory so you use the undocumented numeric parameter, you could do that with this method too.
You'd have to extend the context translation setup to change to the passed in territory and then put it back.
Since all the "smarts" only need to happen once and are then kept for the duration of the session it should scale well for many values.
Rather than relying on a 3-character truncation, I simply add both the full name and the abbreviation to the context.

Also, I didn't (but could have) empty the context on each call of the set. So using this you could populate all of the days of the week for every language.

That may or may not be considered a feature, but is easily reversible if you didn't want that.

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

Session altered.

SQL> CREATE OR REPLACE CONTEXT my_nls_context

2 USING my_nls_contexts;

Context created.

SQL>

SQL> CREATE OR REPLACE PACKAGE my_nls_contexts

2 AS

3 PROCEDURE set_translations(p_language IN VARCHAR2);

4 END;

5 /

Package created.

SQL>

SQL> CREATE OR REPLACE PACKAGE BODY my_nls_contexts

2 AS

3 PROCEDURE set_translations(p_language IN VARCHAR2)

4 IS

5 BEGIN

6 FOR i IN 1 .. 7

7 LOOP

8 DBMS_SESSION.set_context('my_nls_context',

9 TO_CHAR(DATE '2017-03-07' + i, 'fmDAY', 'NLS_DATE_LANGUAGE=' || p_language),

10 TO_CHAR(DATE '2017-03-07' + i, 'DY'));

11 DBMS_SESSION.set_context('my_nls_context',

12 TO_CHAR(DATE '2017-03-07' + i, 'DY', 'NLS_DATE_LANGUAGE=' || p_language),

13 TO_CHAR(DATE '2017-03-07' + i, 'DY'));

14 END LOOP;

15 END;

16 END;

17 /

Package body created.

SQL> begin

2 my_nls_contexts.set_translations('French');

3 end;

4 /

PL/SQL procedure successfully completed.

SQL> select next_day(date '2017-03-07', sys_context('my_nls_context','Mardi')) d from dual;

D

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

2017-03-14 Tuesday

SQL> select next_day(date '2017-03-07', sys_context('my_nls_context','Lundi')) d from dual;

D

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

2017-03-13 Monday

SQL> begin

2 my_nls_contexts.set_translations('German');

3 end;

4 /

PL/SQL procedure successfully completed.

SQL> select next_day(date '2017-03-07', sys_context('my_nls_context','Montag')) d from dual;

D

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

2017-03-13 Monday

SQL>

Sven W. · Mar 8, 2017 11:01AM

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

another workaround is

select trunc(date '2017-08-01' - 1,'iw') + 1 + case when trunc(date '2017-08-01' - 1,'iw')+1 < date '2017-08-01' then 7 else 0 end as next_tue from dual;

It is nls independent, because It avoids any type of datatype conversions (and uses iw). But somehwat difficult to read.

mathguy · Mar 8, 2017 12:05PM

The solution, like my hacks, does depend on NLS settings to some degree - if we insist on passing 'Tuesday' as an argument, and not "second day of the ISO week". There is still a translation to convert the name 'Tuesday' to the number 2. (Again, to be clear - I did the same in my own "hacks".)

The code would need some changes, even if the "input" is the ISO day number. For one thing, next_day() returns the same time-of-day as its date input, while the solution you offered truncates to midnight. Also, with the formula exactly as written, if the input is a Tuesday this solution returns that day, not a week later as next_day() does. (Also, regardless of the formula, having a strict inequality, <, with truncated date on the left and non-truncated date on the right is going to be incorrect in general, since the result will depend on whether the input date has a non-zero time-of-day. But the formula as written doesn't work even if we restrict the whole discussion to truncated dates.)

Something like this should work ( dt is the input date):

trunc(dt, 'iw') + (dt - trunc(dt)) + case when dt - trunc(dt, 'iw') < 1 then 1 else 7 + 1 end-- The magic number 1 appears three times in the query (highlighted in red).  -- The magic number should be 0 for Monday, 1 for Tuesday, etc.

Cheers, mathugy

mathguy · Mar 8, 2017 12:54PM

I ended up writing a function for this. I wasn't going to (there is really no point in us writing our own functions to work around a limitation that Oracle themselves could easily fix - not unless one has a pressing need, and I don't). But I have only written one function so far (posted last year in a thread on implementing a prime numbers algorithm), so I can use the practice.

I offer it here for critique. In particular I am not pleased by the way I achieved one of my goals: making the system raise the "relevant" error when the user inputs an invalid nls parameter or an invalid day-of-the-week. I marked those spots specifically in the code, with comments about what I did. (I read about error handling both in Steven Feuerstein's book and in BluShadow's article in this community, and I still can't remember - and couldn't find quickly - if I can raise a system error directly from my code...)

My goals were:

Write a function my_next_day with three parameters: an input date, the name of a day of the week, and an nls parameter to pass in the language. As in other date functions, the nls parameter should be optional - it should default to the invoking session's NLS_DATE_LANGUAGE.

Allow the same syntax for the nls parameter as in other date functions: 'nls_date_language = French' and 'nls_date_language = ''French''' should both work, etc.

Also, there should be flexibility for the name of the day-of-the-week (as there is in the system function NEXT_DAY); that is, if an initial segment of the argument passed in matches a valid name in either 'dy' or 'fmday' format, it should be accepted.

Here's what I came up with. I did some testing and it seems to do everything I wanted, but perhaps not as efficiently as it could - and I have some doubt about the way I handled exceptions. (I was lazy - I didn't want to write my own, I wanted to piggy-back on system exceptions, and I am guessing that there should be better ways than I did.)

create or replace function my_next_day (
    dt        in date    ,
    dow       in varchar2,
    nls_param in varchar2  default null
  ) return varchar2
  deterministic
as
  ldow     varchar2(200)  := lower(dow);
  lvl      number         := 1;
  flag     boolean        := false;
  ret_dt   date;
  day_name varchar2(200);
begin
  if nls_param is null
  then
    ret_dt := next_day(dt, dow);
  else
        -- the next statement is for testing purposes only: if the nls_param is invalid,
        -- the system exception for invalid nls parameter will be raised.
        -- IS THERE A BETTER WAY TO ACHIEVE THIS?  day_name is not used anywhere else.
    select to_char(dt, 'dy', nls_param) into day_name from dual;    loop
    exit when flag = true or lvl = 8;
      if    ldow like to_char(dt + lvl, 'dy'   , nls_param) || '%'
         or ldow like to_char(dt + lvl, 'fmday', nls_param) || '%'
      then
        ret_dt := dt + lvl;
        flag   := true;
      end if;
      lvl := lvl + 1;
    end loop;
    if flag = false
    then
        -- the next statement is intentionally invalid - I use it to force
        -- the system to throw the "not a valid day of the week" error.
        -- IS THERE A BETTER WAY TO ACHIEVE THIS?        
      ret_dt := next_day(dt, 'not a valid day of the week');
    end if;
  end if;
  return ret_dt;
end;
/

Jarkko Turpeinen · Mar 8, 2017 1:01PM

Hi,

Question: Is there a better way?

without given measures anything will do.

Why not create Business Object to handle this simple task. In other words wrapped code or technique you already described.

But hey, it is called Business Object so it sounds much cooler than functions etc. (my opinion)

Here it is.

create or replacetype Next is object (  Monday date  ,  Tuesday date  ,  Wednesday date  ,  Thursday date  ,  Friday date  ,  Saturday date  ,  Sunday date  ,  constructor function Next( FromDate date default sysdate ) return Self as result);/create or replacetype body Next is  --  constructor function Next( FromDate date default sysdate ) return Self as result  is    -- FromDate offset date    -- DayNumber from 0 to 6    function Weekday( FromDate date, DayNumber number ) return date    is      AnyMonday date default date '2016-08-01';    begin      return next_day(         FromDate        ,to_char( AnyMonday + DayNumber, 'day' )      );    end;  --  begin    Self.Monday     := Weekday( FromDate, 0 );    Self.Tuesday    := Weekday( FromDate, 1 );    Self.Wednesday  := Weekday( FromDate, 2 );    Self.Thursday   := Weekday( FromDate, 3 );    Self.Friday     := Weekday( FromDate, 4 );    Self.Saturday   := Weekday( FromDate, 5 );    Self.Sunday     := Weekday( FromDate, 6 );    return;  end;end;/-- testsselect Next().Tuesday as "Next Tuesday" from dual;select Next( date '2017-3-8' ).Monday as "Next Monday" from dual;drop type Next;Type NEXT compiledType body NEXT compiledNext Tuesday------------14-MAR-17   Next Monday-----------13-MAR-17  Type NEXT dropped.

Edited body bit cleaner.

Paulzip · Mar 8, 2017 1:05PM

Looks very similar to my solution! Just saying.... I suspect my select version is faster than your PL/SQL loop, even with the short circuit, but it is kind of doing the same thing - Oracle is very quick at SQL filtering, it has been optimised for decades and until the cows come home. I look for the first 3 chars as I found a doc somewhere that said that was the min number of letters for day uniqueness across languages. I put exception handling in just in case that isn't the case. "Like" isn't particularly quick as it isn't checking in a static way.

if Flag = False (yuk),

if not Flag...

Forum Stats

Quick Links

Categories

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

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

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

Answers