next_day

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.

(more…)