2 Replies Latest reply: Nov 15, 2005 9:02 AM by 427608 RSS

    TO_DATE(TO_CHAR(sysdate, 'MM/DD/YYYY'), 'MM/DD/YYYY')

    427608
      Hi all,
      I am working with a Oracle 9i R2 database

      SQL>select TO_CHAR(sysdate, 'MM/DD/YYYY') CHRDATE from dual;

      CHRDATE
      ----------
      11/14/2005

      SQL>select TO_DATE(TO_CHAR(sysdate, 'MM/DD/YYYY'), 'MM/DD/YYYY') mydate from dual;

      MYDATE
      ---------
      14-NOV-05

      I want to retain the 4 digit year. Please suggest what I am doing incorrect.

      Thanks
        • 1. Re: TO_DATE(TO_CHAR(sysdate, 'MM/DD/YYYY'), 'MM/DD/YYYY')
          user346369
          Your select statement,
          SQL>select TO_DATE(TO_CHAR(sysdate,...
          is equivalent to
          select sysdate...
          And whenever you select a date, SQL Plus has to convert it to a character format before it can display it on the SQL Plus output screen.

          The four-digit year IS being retained internally ...until you display it on the screen. If you just set the default date format for displaying dates to include the four-digit year, you will see the full year:
          SQL> select SYSDATE mydate from dual;
          
          MYDATE
          ---------
          14-NOV-05
          
          SQL> alter session set nls_date_format = 'MM/DD/YYYY';
          
          SQL> select SYSDATE mydate from dual;
          
          MYDATE
          ----------
          11/14/2005
          
          SQL> select TO_DATE(TO_CHAR(sysdate, 'MM/DD/YYYY'), 'MM/DD/YYYY') mydate from dual;
          
          MYDATE
          ----------
          11/14/2005
          • 2. Re: TO_DATE(TO_CHAR(sysdate, 'MM/DD/YYYY'), 'MM/DD/YYYY')
            427608
            Thanks Steve,
            Makes sense now..