Skip to Main Content

Database Software

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,040 views