6 Replies Latest reply: Apr 11, 2011 3:25 PM by Peter Gjelstrup RSS

    Day of week (1-7) and NLS settings

    Peter Gjelstrup
      This is embarrassing, but I am going nuts here.


      Today is monday. Around here, this is the first day of the week.

      So,
      SQL> select to_char(sysdate, 'd') from dual;
      
      TO_CHAR(SYSDATE,'D')
      --------------------
      2                   
      1 row selected.
      Fine, that is probably becuase of my NLS settings:
      SQL> select * from nls_session_parameters
      where parameter = 'NLS_DATE_LANGUAGE';
      
      PARAMETER                      VALUE                                   
      ------------------------------ ----------------------------------------
      NLS_DATE_LANGUAGE              AMERICAN                                
      1 row selected.
      Let's change it then, into something where people know that monday is the first day of the week ;)
      SQL> alter session set nls_language = german;
      Session altered.
      
      SQL> select to_char(sysdate, 'd') from dual;
      
      TO_CHAR(SYSDATE,'D')
      --------------------
      2                   
      1 row selected.
      No luck, how about
      SQL> select to_char(sysdate, 'd', 'NLS_DATE_LANGUAGE = danish') from dual;
      
      TO_CHAR(SYSDATE,'D','NLS_DATE_LANGUAGE=DANISH')
      -----------------------------------------------
      2                                              
      1 row selected.
      Wrong variable, maybe. How about NLS_TERRITORY
      SQL> alter session set nls_territory = 'DENMARK';
      Session altered.
      
      SQL> select to_char(sysdate, 'd') from dual;
      
      TO_CHAR(SYSDATE,'D')
      --------------------
      1                   
      1 row selected.
      Great! - But I don't like to alter session like this, and
      SQL> select to_char(sysdate, 'd', 'NLS_TERRITORY = denmark') from dual:
      select to_char(sysdate, 'd', 'NLS_TERRITORY = denmark') from dual
                                                                   *
      Error at line 1
      ORA-12702: invalid NLS parameter string used in SQL function
      Dang, out of ideas. Am I just on a mission impossible here?


      Regards
      Peter
      BANNER                                                          
      ----------------------------------------------------------------
      Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
        • 1. Re: Day of week (1-7) and NLS settings
          Centinul
          Peter,

          From the SQL Reference (Format Models):
          The datetime format element D returns the number of the day of the week (1-7). The day of the week that is numbered 1 is specified implicitly by the initialization parameter NLS_TERRITORY.
          From the SQL Reference (TO_CHAR(datetime)):
          The 'nlsparam' argument specifies the language in which month and day names and abbreviations are returned. This argument can have this form:
          'NLS_DATE_LANGUAGE = language' >

          So 'D' is controlled by NLS_TERRITORY and the TO_CHAR function can only handle NLS_DATE_LANGUAGE.

          So it looks like ALTER SESSION is your only way out here.
          • 2. Re: Day of week (1-7) and NLS settings
            Hoek
            Great! - But I don't like to alter session like this
            Well, you could always
            SQL> select t.value
              2  ,      case 
              3            when t.value != 'DENMARK' 
              4            then to_char(sysdate-1, 'd')
              5            else to_char(sysdate, 'd')
              6         end
              7  from   nls_session_parameters t
              8  where  t.parameter = 'NLS_TERRITORY';
            
            VALUE                                    C
            ---------------------------------------- -
            AMERICA                                  1
            ;)
            • 3. Re: Day of week (1-7) and NLS settings
              Peter Gjelstrup
              Thanks Centinul,

              Did not read far enough. It is even clearer in the 11.2 reference where it for D element says
              Day of week (1-7). This element depends on the NLS territory of the session.
              In 10.2 reference it is just
              Day of week (1-7).
              @Hoek, wouldn't that be great, but unfortunately it can be all sorts of things "Around here" not all are Danish ;)


              I have decided to use to_char( , 'day', 'nls_language='), instead and then sniff for saturday/sunday.


              Thanks
              Peter
              • 4. Re: Day of week (1-7) and NLS settings
                MichaelS
                Maybe
                SQL> select case to_char (sysdate, 'FmDay', 'nls_date_language=english')
                          when 'Monday' then 1
                          when 'Tuesday' then 2
                          when 'Wednesday' then 3
                          when 'Thursday' then 4
                          when 'Friday' then 5
                          when 'Saturday' then 6
                          when 'sunday' then 7
                       end d
                  from dual
                /
                         D
                ----------
                         1
                1 row selected.
                • 5. Re: Day of week (1-7) and NLS settings
                  Frank Kulash
                  Hi, Peter,

                  What is the problem?
                  Do you just want an expression that, given a DATE, will return an integer (1 for Monday, ..., 7 for Sunday), independent of NLS settings?
                  If so:
                  1 + TRUNC (dt)
                    - TRUNC (dt, 'IW')
                  • 6. Re: Day of week (1-7) and NLS settings
                    Peter Gjelstrup
                    return an integer (1 for Monday, ..., 7 for Sunday), independent of NLS settings?
                    That's right Frank,

                    and cool, thank you!

                    @Michaels, thanks for reminding me about fill mode, which I of course forgot about it

                    Regards
                    Peter