Cannot use NEXT_DAY function in Fusion BI Publisher Data model

Abhradeep Haldar Ext

Summary

Cannot use NEXT_DAY function in Fusion BI Publisher Data model

Content

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

please mark it as correct answer.

Abhradeep Haldar Ext

Thank you so much. This works great.

Regards,

Abhradeep

Venkat Thota - BIP

can you try this ,

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

Thanks

Manish_Chauhan

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

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

https://community.oracle.com/products/oracleanalytics/discussion/comment/66363#Comment_66363

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

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;