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..