Getting another weekdays date aside from oracle’s next_day()


Oracle does provide a sql-method called next_day(p1, p2) to return the date of the first weekday named by p2 that is later than the date p1. A common issue with next_day() is, however, that the weekday named in p2 constitutes a non-abstract, here language-dependent (but on tests non-case-sensitive), value. That is, calling next_day() with p2='MONTAg' in a session with nls_language='FRENCH' will throw exception ORA-01846: ce n’est pas un jour de semaine valide or 01846. 00000 – “not a valid day of the week”.

You may now come up with an approach to comprise any environment setting of nls_language and any necessary value of p2 in a large switch block but… to no avail. Whoever is going to determine what range of environment setting of nls_language will be in effect over the lifetime of an application. In particular, since nls_language depents on the client side settings of the database, nls_language may vary widely according to the locale on connect.

Ok, yet another approach, observed quite often, is an intermediate set and reset of nls_language using alter session set nls_language=.... Jep, does work, but may cause even headache (and spotted very often too) iff you, in case, forget the reset altogether and your session code proceeds under the changed language environment.

The solution I prefer for getting the next date of a fixed weekday from now on (or anything you may bind to p1) is using another sql-method called trunc(p1, p2). Of course, anyone knows trunc(), truncating a date value by the time mantissa and like so. But trunc() may deliver even more. Specifically, handing in p2='IW' will return the same day of the week as the first day of the ISO week, which is monday, independent of the session language. Since one day equals integer 1 in oracle (and many other calendar implementations), a result + 1 gives tuesday, a result + 2 gives wednesday and so on. See the code to follow:

-- initial state
select * from nls_session_parameters
  where parameter = 'NLS_LANGUAGE';
NLS_LANGUAGE	GERMAN

-- works, although in english
select trunc(next_day(sysdate, 'MONDAY')) from dual;
25.01.16 00:00:00

-- works, should do
select trunc(next_day(sysdate, 'MoNTAg')) from dual;
25.01.16 00:00:00

-- throws
select trunc(next_day(sysdate, 'LUNDI')) from dual;
ORA-01846: Kein gültiger Wochentag

-- have it the french way
alter session set nls_language = 'FRENCH';
select * from nls_session_parameters
  where parameter = 'NLS_LANGUAGE';
NLS_LANGUAGE	FRENCH

-- works, should do now
select trunc(next_day(sysdate, 'LuNdI')) from dual;
25.01.16 00:00:00

-- the preferred way
select trunc(sysdate+7, 'IW') from dual;
25.01.16 00:00:00

-- have a job starting every saturday 9 am
declare job binary_integer; begin
  dbms_job.submit( job, 'yet_another_code(''ART'');',
    trunc(sysdate, 'IW') + 5 + 1/24*9,
    'TRUNC(SYSDATE+7, ''IW'') + 5 + 1/24*9', false);
  commit;
end;
/

Ps. trunc() allows for another interesting truncating units at p2, here 'W' to return the first day of the month and 'WW' to return the first day of the year, respectively, and this way provides everything you need to do your calendar calculations properly based on a fixed and reliable (leap year independent…) point in time.

Have fun, Peter

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s