Dylan Jackson Dylan Jackson - 5 months ago 27
SQL Question

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?

Answer

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.

Comments