2 Replies Latest reply: Sep 26, 2013 3:00 AM by brianotn RSS

    DATE arithmetic in SQL Workshop v SQL Developer - different results

    brianotn

      Hi

       

      I want a date picker to default to the Monday of the current week. I'm basing it on TO_CHAR with a format mask so this should give me the number of the day of the week:

       

      SELECT SYSDATE, to_char(SYSDATE, 'D')
      FROM dual
      

       

      In APEX SQL Workshop, I get

       

      09/25/2013  4
      

       

      which is wrong - today is Wednesday.

       

      The same code in SQL Developer gives

       

      25-SEP-13  3
      

       

      which is correct.

       

      Am I missing some APEX setting here which determines when the start of the week is (Monday v Sunday)?

       

      Many thanks

       

      Brian

       

      Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

      APEX 4.2.2.00.11

      Windows 2008 R2 Enterprise Server

        • 1. Re: DATE arithmetic in SQL Workshop v SQL Developer - different results
          Mike Kutz

          I'm guessing this: (from Oracle documentation)

          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.

           

          For me:

          select sysdate, to_char(sysdate,'D'), to_char(sysdate,'Day')  from dual;

          25-SEP-13    4    Wednesday

          Which is (for me) correct. (Sunday == 1 )

           

          What have we learned?

          NLS_TERRITORY can be different depending on who/how/when the DB connection is made.

          This is the same as NLS_DATE_FORMAT.

           

          Solution:

          Never ever trust anything that depends on them unless the code explicitly sets the value.

          This is why you ALWAYS provide the date format when converting from String to Date using the TO_CHAR();

           

          MK

          • 2. Re: DATE arithmetic in SQL Workshop v SQL Developer - different results
            brianotn

            Thanks Mike

             

            I hadn't realised that the first day of the week depended on NLS_TERRITORY

             

            I've taken your advice and used the NEXT_DAY function instead

             

            Brian