4 Replies Latest reply: Apr 5, 2013 2:03 PM by Frank Kulash RSS

    Converting Varchar2 to Date type and retaining the format

    994903
      I have a column which is of type Varchar2 in one table. This column contains a date string with format YYYY-MM-DD HH24:MI:SS.

      I'm trying to take this same value and insert it into another table where the column is of DATE type. Not only that I also want to retain the format.
      How to do this?

      When I use TO_DATE operator it inserts in 'DD-MM-YY' format.
        • 1. Re: Converting Varchar2 to Date type and retaining the format
          rp0428
          >
          I have a column which is of type Varchar2 in one table. This column contains a date string with format YYYY-MM-DD HH24:MI:SS.

          I'm trying to take this same value and insert it into another table where the column is of DATE type. Not only that I also want to retain the format.
          How to do this?
          >
          You can't - DATE values do NOT have a 'format'; the value is stored encoded in 7 binary bytes.
          >
          When I use TO_DATE operator it inserts in 'DD-MM-YY' format.
          >
          No - it doesn't. The value is only formatted when you extract it and display it using a tool like sql*plus.

          You can use the TO_CHAR function to display the value the way you want
          SELECT TO_CHAR(myDate, 'YYYY-MM-DD HH24:MI:SS') FROM myTable;
          
          SQL> select sysdate, to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') formattedDate from dual;
          
          SYSDATE   FORMATTEDDATE
          --------- -------------------
          05-APR-13 2013-04-05 11:19:31
          
          SQL>
          • 2. Re: Converting Varchar2 to Date type and retaining the format
            Ramin Hashimzadeh
            Not only that I also want to retain the format
            why? you can any time convert it to any date format using function to_char
            • 3. Re: Converting Varchar2 to Date type and retaining the format
              EdStevens
              991900 wrote:
              I have a column which is of type Varchar2 in one table. This column contains a date string with format YYYY-MM-DD HH24:MI:SS.

              I'm trying to take this same value and insert it into another table where the column is of DATE type. Not only that I also want to retain the format.
              How to do this?

              When I use TO_DATE operator it inserts in 'DD-MM-YY' format.
              Ever consider a career as a "straight man"? You are exactly the guy this was written for:

              see: [url http://edstevensdba.wordpress.com/2011/04/07/nls_date_format/] But I want to store my date as ... 
              • 4. Re: Converting Varchar2 to Date type and retaining the format
                Frank Kulash
                Hi,

                If, for some reason, you really, really need to associate a format with a DATE, then store the format in a separate column. For example:
                CREATE TABLE ...
                ,   entry_date         DATE
                ,   entry_date_format  VARCHAR2 (30)  ...
                When you need to display the dates in that format, you can do something like this:
                SELECT  ...
                ,     TO_CHAR ( entry_date
                          , NVL ( entry_date_format
                                , 'YYYY-MM-DD HH24:MI:SS'     -- Default format
                                )
                          )  AS formatted_entry_date
                ...
                I don't know if I've ever seen a situation where this was needed.