This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,964 Users
  • 2,269,775 Discussions


Get consistent response from to_char 'D' using different NLS's

Pericles Member Posts: 80 Blue Ribbon


I'm using to_char(date,'D') in a package procedure to get the day of the week of a given date. If something happens on weekend (to_char(date,'D') is 1 or 7 ) the procedure should do A else B.

I'm facing some issues because the procedure will be called from an APEX 22.1 session affected by user's NLS. If the user's NLS is EN-US to_char('23-JUL-2022','D') returns 7, but if the user NLS is SP-AR then the same function returns 6, affecting the result of the procedure

I've found that DBMS_SESSION.SET_NLS can be used, but I'm quite worried about what would be the impact of that change for the end user.

Wonder if there is another way to find out if a date is Saturday or Sunday no mater NLS format.

Appreciate your comments.


Best Answer

  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond

    You could also tell TO_CHAR explicitly to use a particular language, like this:

    TO_CHAR (dt, 'Dy', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('Sat', 'Sun')

    regardless of what the system or session NLS settings are. However, you can't tell TO_CHAR to use a particular NLS_TERRITORY, which is what governs the 'D' format.