How to Enable button only selected Hours and Days oracle apex-CodePudding

I have created a button on Oracle Apex 21.1. I want to enable the button only under selected Hours and Days. I have applied "Server-side condition" with type "Rows returned".

Hours from Evening 04:00:00 PM Till Next Day Morning 08:00:00 AM. Days From Monday to Saturday.

From this query, the button disappears after 08:00:00 PM Server / System Date. I have mentioned in the where clause that is button will disappear after 08:00:00 AM

SQL QUERY:

 SELECT TO_CHAR(sysdate, 'HH:MI:SS PM'), REPLACE(TO_CHAR(sysdate, 'DAY'), ' ')
 FROM dual
 WHERE TO_CHAR(sysdate, 'HH:MI:SS PM') >= '04:00:00 PM'
 AND TO_CHAR(sysdate, 'HH:MI:SS PM') <= '08:00:00 AM'
 AND REPLACE(TO_CHAR(sysdate, 'DAY'), ' ') IN ('MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 
 'FRIDAY', 'SATURDAY')
 ;

CodePudding user response：

For a question like this the easiest is to generate a list of dates and check if the where clause works ok. Generate a list of dates using CONNECT BY LEVEL < x. In my case I used a CASE statement instead of a where clause but the result is the same.

I changed a couple of things to make it less confusing:

change date format to HH24:MI:SS - that is just an alphanumeric string so there is no room for error when comparing strings. In your case, '03:01:00 AM > '02:01:00 PM' since your comparing strings. I doubt that is what you want. Better to use 24 hour format.
use FMDAY for the days: that trims the whitespace.

That being said, here is how to check a list of dates:

 WITH some_dates (dt_str, dt) AS
 (
 SELECT TO_CHAR(SYSDATE   (LEVEL * 5)/24,'FMDAY DD-MON-YYYY HH24:MI'), SYSDATE   (LEVEL * 5)/24 FROM DUAL
 CONNECT BY LEVEL < 40
 )
 SELECT DT_STR,
   CASE WHEN 
     (TO_CHAR(dt,'HH24MI') >= '1600' OR TO_CHAR(dt,'HH24MI') <= '0800') AND
     (TO_CHAR(dt,'FMDAY') NOT IN ('SATURDAY','SUNDAY'))
     THEN 'Y' ELSE 'N' END
   FROM some_dates;

This works. A 'Y' is shown for weekdays and Hours from Evening 04:00:00 PM Till Next Day Morning 08:00:00 AM. A 'N' for times that don't need to be shown.

So if we use that case statement in the where clause of statement in a server-side condition of type "rows returned" this gives:

SELECT 1 FROM DUAL WHERE
   CASE WHEN 
     (TO_CHAR(sysdate,'HH24MI') >= '1600' OR TO_CHAR(sysdate,'HH24MI') <= '0800') AND
     (TO_CHAR(sysdate,'FMDAY') NOT IN ('SATURDAY','SUNDAY'))
     THEN 'Y' ELSE 'N' END = 'Y'

CodePudding user response：

Adding on top of @koen-lostrie

I would suggest you compare dates with dates. What I would do is compare sysdate with the start and end of your business hours for today like:

select 1
  from dual
 where sysdate between to_date( to_char(sysdate, 'YYYY-MM-DD') || '0800', 'YYYY-MM-DDHH24MI')
                   and to_date( to_char(sysdate, 'YYYY-MM-DD') || '1600', 'YYYY-MM-DDHH24MI')
   and to_char(sysdate,'FMDAY') not in ('SATURDAY','SUNDAY')

You may also want to add this to your process' condition to prevent someone from submitting something after 16:00 (in case they would have accessed the page at 15:59 with an enabled button)