Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Add ISO day to format model for date functions

Thorsten KettnerFeb 4 2019 — edited Feb 4 2019

In order to have a reliable day number, I suggest adding the format 'ID' to the format models to represent the ISO day (according to ISO 8601), where 1 is always Monday and 7 is always Sunday.

SELECT * FROM surcharges WHERE isodaynum = TO_CHAR(SYSDATE, 'ID');

Explanation:

So far there only exists a single D for format masks, meaning "day of week (1-7)". Which weekdays the numbers 1-7 stand for, however, depends on the session's NLS_TERRITORY setting. So while we can use

SELECT 'This is the ' || TO_CHAR(SYSDATE, 'D') || '. day of the week.' FROM DUAL;

We cannot use

SELECT * FROM surcharges WHERE daynum = TO_CHAR(SYSDATE, 'D');

because the result would be non-deterministic. It would return either the Sunday or the Monday surcharges depending on current session settings.

Comments

Post Details

Added on Feb 4 2019
1 comment
1,487 views