Cannot use NEXT_DAY function in Fusion BI Publisher Data model

Abhradeep Haldar Ext · Oct 12, 2017 7:00AM

Summary

Hi All,

I am trying to run the below query to get the next Saturday in a BI Publisher Data model.

SELECT TRUNC (NEXT_DAY (TRUNC (SYSDATE, 'IW'), 'SATURDAY')) FROM DUAL

However it keeps giving me the below error.

oracle.xdo.servlet.data.DataException: oracle.xdo.XDOException: java.sql.SQLDataException: ORA-01846: not a valid day of the week

Is there a technical limitation here? The query in question works perfectly when I run it in SQL Developer.

How do I calculate the next SATURDAY from today in BI Publisher.

Regards,

Abhradeep

Venkat Thota - BIP · Oct 13, 2017 7:00AM

please mark it as correct answer.

Abhradeep Haldar Ext · Oct 13, 2017 7:00AM

Thank you so much. This works great.

Regards,

Abhradeep

Venkat Thota - BIP · Oct 13, 2017 7:00AM

can you try this ,

select TRUNC(sysdate, 'iw')+5 AS iso_week_start_date from dual;

Thanks

Manish_Chauhan · Apr 15, 2025 12:25PM

Hi @Abhradeep Haldar Ext- I have a similar requirement, can you tell how did you fix this query ?

SELECT TRUNC (NEXT_DAY (TRUNC (SYSDATE, 'IW'), 'SATURDAY')) FROM DUAL

Manish_Chauhan · Apr 15, 2025 12:28PM

Hi @Venkat Thota - BIP- I have requirement to pull first Sunday of every month, trying below query but its not working as expected, can you help ?

SELECT NEXT_DAY(TRUNC(SYSDATE,'MM') - 1,TO_CHAR(DATE '2025-04-15','DAY'))

  ,NEXT_DAY(TRUNC(SYSDATE,'MM') - 1,TO_CHAR(DATE '2025-04-15','DAY')) + 7 Next_Sunday

FROM dual

Brendan T · Apr 15, 2025 2:49PM

Try this

SELECT NEXT_DAY( TRUNC(to_date('21-MAY-2025'), 'MM') - 1 , 'Sunday' ) FROM dual

And depending on your version of the DB you no longer need the 'FROM dual' 😀

Riyaz Ali-Oracle · Apr 24, 2025 1:11PM

Try Below query:

SELECT
NEXT_DAY(TRUNC(SYSDATE, 'MM') - 1, 'SUNDAY') AS First_Sunday,
NEXT_DAY(TRUNC(SYSDATE, 'MM') - 1, 'SUNDAY') + 7 AS Next_Sunday
FROM dual;

OR

SELECT NEXT_DAY(TRUNC(SYSDATE, 'MM') - 1, 'SUNDAY') AS First_Sunday
FROM dual;