Apex Oracle SQL : How to get Date Picker to from last day to today, and from sunday – monday?

by

Little more information:

On dashboard, we have queries that are set up where it bases the results based on 2 date pickers. The first date picker is from: (Date) and the second is to: (Date).

Currently, have dynamic action to set it from date to sysdate – 1, and that works great for tuesday-friday. However, when it’s a monday morning, I want it to be able to set the from date to the last friday, and to date is simply sysdate.

Is this possible to do and how would I go about doing that? Currently on mondays we just have to manually change the date to last friday, which isn’t too huge of a deal, but when we open 20+ pages it makes it a hassle.

Thanks

As you already have dynamic action which sets FROM and TO dates, modify it so that it checks whether “today” (i.e. date returned by sysdate) is Monday or not.

If it is, find next Friday (with the next_day function) and subtract 7 to get previous Friday. If not, use yesterday.

This is code that does the calculation:

select case when to_char(sysdate, 'DY') = 'MON' then
                 trunc(next_day(sysdate, 'FRI')) - 7
            else 
                 trunc(sysdate) - 1
       end as from_date
from dual;

Leave a Comment