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
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
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; /
trunc() allows for another interesting truncating units at
'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