Validating a day of the week, by name, in Oracle

Question

Given a string representation of a day of the week (i.e. Monday, Mardi, الثلاثاء etc.) and a NLS_DATE_LANGUAGE is there any way of validating that this Day of the Week is valid?

Why is this difficult you ask? Well, if this were just English the obvious thing to do would be something like the following:

if <day_variable> in ('Monday','Tuesday', ...) then
   ...

I'm trying to do this, extensibly, for several countries and as I don't know (and can't be bothered) to write out all the days of the week for all current and future NLS_DATE_LANGUAGE's this isn't really an option.

Next option is an explicit TO_DATE(), which works beautifully with Friday - I suspect that this is a coincidence 'cause it's May, but fails miserably with the rest of the week:

SQL> select to_date('Friday', 'Day') as d from dual;

D
----------
2015-05-01

SQL> select to_date('Monday', 'Day') as d from dual;
select to_date('Monday', 'Day') as d from dual
               *
ERROR at line 1:
ORA-01835: day of week conflicts with Julian date

That's an interesting error... Oracle recommends to:

Remove the day of the week value from the date specification or enter the correct day of the week for the Julian date.

Removing the day of the week isn't really an option as that's all I'm interested and I don't have an incorrect day of the week for my Julian date because I don't have a Julian date.

Workaround's suggested elsewhere confirm and using alternative date formats (Dy, dy, FMDy, etc.) fairly obviously results in the same error.

It appears as though Oracle represents all dates internally as Julian dates and is getting confused in this situation.

How can I validate that a named day is correct in Oracle?

Answer 1

As far as I can tell I'm stuck with running something like the following every time we patch the DB (certainly won't remember to do that) to generate all possible days

select value as language
     , to_char(sysdate + l, 'FMDay', 'nls_date_language=''' || value || '') as name_of_day
  from v$nls_valid_values
 cross join ( select level as l from dual connect by level <= 7 )
 where parameter = 'LANGUAGE'
   and isdeprecated = 'FALSE'
 order by language

Doing this would not be at all extensible as if I were to need the day of the week (i.e. 1, 2, 3, etc.) the value is based on the current NLS_TERRITORY, which means there's no possible consistency.

to_char(sysdate + l, 'D', 'nls_date_language=''' || value || '') as day_of_week

I'd then have to create a non-deterministic, :-(, function to validate whether the string is (in)correct:

create or replace function is_day_of_week (
    PDay in varchar2
  , PDate_Language
    ) return number is

   l_ct number;

begin

   select count(*) into l_ct
     from days_of_the_week
    where name_of_day = PDay
      and language = PDate_Language;

   return l_ct;

end is_day_of_week;

Answer 2

Observe that a string representation of any 7 consecutive calendar dates that contains the day of the week must have the tested day-of-week string as a substring iff the tested string is valid.

Thus to circumvent the error, concatenate the string representations of the week starting with sysdate and test your string against the result:

SELECT CASE INSTR(
               to_char ( sysdate + 0, 'Day, DD.MM.YYYY' )   
            || to_char ( sysdate + 1, 'Day, DD.MM.YYYY' )
            || to_char ( sysdate + 2, 'Day, DD.MM.YYYY' )
            || to_char ( sysdate + 3, 'Day, DD.MM.YYYY' )
            || to_char ( sysdate + 4, 'Day, DD.MM.YYYY' )
            || to_char ( sysdate + 5, 'Day, DD.MM.YYYY' )
            || to_char ( sysdate + 6, 'Day, DD.MM.YYYY' )
          , '<the_string_to_test>' )
         WHEN 0 THEN 'invalid'
         ELSE        'valid'
       END  isvalid
  FROM DUAL
     ;

As a refinement, delimiters around the day part if the format strings and the tested string can guard against false positives where the tested string is a substring of a valid day name.

SELECT CASE INSTR(
               to_char ( sysdate + 0, '.FMDay., DD.MM.YYYY' )   
            || to_char ( sysdate + 1, '.FMDay., DD.MM.YYYY' )
            || to_char ( sysdate + 2, '.FMDay., DD.MM.YYYY' )
            || to_char ( sysdate + 3, '.FMDay., DD.MM.YYYY' )
            || to_char ( sysdate + 4, '.FMDay., DD.MM.YYYY' )
            || to_char ( sysdate + 5, '.FMDay., DD.MM.YYYY' )
            || to_char ( sysdate + 6, '.FMDay., DD.MM.YYYY' )
          , '.' || '<the_string_to_test>' || '.')
         WHEN 0 THEN 'invalid'
         ELSE        'valid'
       END  isvalid
  FROM DUAL
     ;

Edit/Note

In the latter example note that ...

• Not all delimiters work ( tested with . [works] and | [fails] )
• FMDay must be used as format specifier to trim trailing whitespace from the day-of-week names

Edit #2

Here is the same (almost) without the need of string operations:

select 
  case when upper(:day_name) in 
  (
    select to_char(sysdate + 0, 'FMDAY', 'NLS_DATE_LANGUAGE=''' || :nls_date_lang || '''') from dual union all
    select to_char(sysdate + 1, 'FMDAY', 'NLS_DATE_LANGUAGE=''' || :nls_date_lang || '''') from dual union all
    select to_char(sysdate + 2, 'FMDAY', 'NLS_DATE_LANGUAGE=''' || :nls_date_lang || '''') from dual union all
    select to_char(sysdate + 3, 'FMDAY', 'NLS_DATE_LANGUAGE=''' || :nls_date_lang || '''') from dual union all
    select to_char(sysdate + 4, 'FMDAY', 'NLS_DATE_LANGUAGE=''' || :nls_date_lang || '''') from dual union all
    select to_char(sysdate + 5, 'FMDAY', 'NLS_DATE_LANGUAGE=''' || :nls_date_lang || '''') from dual union all
    select to_char(sysdate + 6, 'FMDAY', 'NLS_DATE_LANGUAGE=''' || :nls_date_lang || '''') from dual
  ) then 'valid' else 'invalid'
  end  isvalid
from dual;