Hi,
I have a table that is configured to send out SMS messages on the next working day. Generally speaking, no messages are sent on a Saturday or Sunday but that can be changed upon the configuration of a base table.
The weekday is held in the column GENCODES_SYS.CODE. I need to order the values of that field in the order of the week starting with Monday with 'HOLIDAY' at the bottom hence the use of the next_day function.
This has been working fine with the following:
SELECT *
FROM GENCODES_SYS
WHERE type = 'SMS_CONTACT'
ORDER BY CASE code
WHEN 'HOLIDAY' THEN TRUNC (next_day (SYSDATE, 'SUNDAY')) + 8
ELSE TRUNC (next_day (TRUNC (next_day (SYSDATE, 'SUNDAY')), code))
END;
We have recently rolled out an application to different sites across Europe each with a different NLS_TERRITORY setting so it is falling over with the hard coding of SATURDAY and SUNDAY in the next_day function. For example, in France Saturday and Sunday are Samedi and Dimanche respectively.
Is there an easier way that this functionality could be achieved without using the next_day function. I have had a look at using day numbers but then that too can be affected by the NLS_TERRITORY.
Thanks in advance,
Chris