current_date auto change when it's Monday

PJ CHEN · Feb 5, 2018 12:15AM

hi all,

I have a question about how to change the current_date,

I used CURRENT_DATE-2 to get the data,

however, I wounder can it auto update to CURRENT_DATE-4 when it's Monday

for example, in R , I will try like this :

if(weekdays.Date(Sys.Date())=="Monday"){

datadate <-Sys.Date()-4}else(datadate <-format(Sys.Date()-2)

Many thanks!!

擷取.PNG

Robert Angel · Feb 5, 2018 4:08AM

Hi,

you have a case statement in OBIEE

you also have a DayOfWeek function.

So, not hard to do the equivalent.

Joel · Feb 5, 2018 4:56AM

You'll need something like this:

CASE WHEN DAYOFWEEK(CURRENT_DATE) = 2  THEN TIMESTAMPADD(SQL_TSI_DAY, -4, CURRENT_DATE)  ELSE TIMESTAMPADD(SQL_TSI_DAY, -2, CURRENT_DATE)  END

PJ CHEN · Feb 5, 2018 5:22AM

THANKS YOU SO MUCH!!!

PJ CHEN · Feb 5, 2018 5:22AM

THANKS!

MFA for Oracle Community