Oracle: how to check if system date not in working hours-CodePudding

how to check if system date within Friday 5pm and Monday 8am in oracle sql. I want to implement a trigger to add update restrictions on a table.

CodePudding user response：

found a working solution. it may help to someone.

IF to_char(sysdate, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('SAT','SUN') 
        THEN
            raise_application_error(-20000, 'message...');
        
    ELSIF to_char(sysdate, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('FRI') AND to_number(to_char(sysdate, 'hh24MIss')) > 170000
        THEN
            raise_application_error(-20000, 'message...');
            
    ELSIF to_char(sysdate, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('MON') AND to_number(to_char(sysdate, 'hh24MIss')) < 90000
        THEN
            raise_application_error(-20000, 'message...');
    END IF;

CodePudding user response：

We can use the following query to find out whether or not we are between Monday day 1 at 7 am : 107 and Friday at 17 : 517.

select 
  case 
    when to_char(sysdate, 'DHH24',
 'NLS_DATE_LANGUAGE=ENGLISH') 
    between 107 and 517 then 'week'
    else 'weekend' end "rate"
from dual;