CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
Dylan Jackson
Dylan Jackson

Reputation: 811

PL/SQL between day's of week

I need to write a trigger that prevents insertion between 5pm Friday and 9am Monday.

Am I going down the right track with thinking I need to extract the day/time from the sysdate and do something along the lines of

if
extract (day from sysdate) =>5 
AND extract (hour from sysdate) =>17 
AND extract (day from sysdate) =< 1 
AND extract (hour from sysdate) =<9
THEN
...

Please let me know if this is horribly going down the wrong track.

I find it frustrating that my searches of "specify between days of week" come up fruitless, perhaps someone can suggest better search methods?

Upvotes: 1

Views: 1537

Answers (3)

Flado
Flado

Reputation: 36

Here is a trigger that does the job and does not depend on the NLS settings:

create or replace trigger no_we
before insert or update or delete on dylans_table
for each row
when (sysdate between trunc(sysdate-9/24,'IW')+4+17/24 /*Friday 5pm*/ 
                  and trunc(sysdate-9/24,'IW')+7+ 9/24 /*Monday 9am*/)
begin
  raise_application_error(-20001, 'No working on weekends. Go live your life!');
end;
/

In case somebody wonders, the format specifier 'IW' is the ISO 8601 Week - a type of week that always starts on a Monday.

Upvotes: 0

Szilard Barany
Szilard Barany

Reputation: 1135

I would try:

IF TO_NUMBER( TO_CHAR( SYSDATE, 'DHH24' ) ) > 516 OR
   TO_NUMBER( TO_CHAR( SYSDATE, 'DHH24' ) ) < 109 THEN

or even just

IF NOT TO_NUMBER( TO_CHAR( SYSDATE, 'DHH24' ) ) BETWEEN 109 AND 516 THEN

You need to be aware that the value of D, the day of the week depends on the local calendar, so for example the first day of the week might be Sunday or Monday. Check your NLS settings.

Upvotes: 6

Justin Cave
Justin Cave

Reputation: 231661

Personally, I'd probably break it down into three spans that are easier to code for.

if(      -- Disallow all day Sat & Sun
         to_char( sysdate, 'fmDay' ) in ('Saturday', 'Sunday') )  
    or  (    to_char( sysdate, 'fmDay' ) = 'Friday'
         and extract( hour from sysdate ) >= 17) -- Disallow on Friday after 5
    or  (    to_char( sysdate, 'fmDay' ) = 'Monday'
         and extract( hour from sysdate ) < 9) -- Disallow on Monday before 9
   )
then

This assumes that your NLS settings are always set to English. You can make that more generic but it starts to get more complicated because different countries start the week on different days so 1 may be either Monday or Sunday.

Upvotes: 1

Related Questions