4 Replies Latest reply on May 11, 2010 1:56 PM by 753487

    Date to Timestamp


      I am new to PL/SQL, basically I am a java developer, now writing procedure, need your help.

      I am writing a procedure to take record from one table say MESSAGES and insert into new table SAP_MESSAGES, where MESSAGES table has only date column , where as SAP_MESSAGES has timestamp column, customer wanted to insert timestamp into 2nd table which should have date from 1st table and concatenate 12:00AM to each date andf insert that timestamp value in 2nd table(as 1st table is only providing date)

      how can I do that in PL/SQL, my cursor in procedures reads the records from 1st table, then I have to set the timestamp variable by getting date value from 1st record.

      can you help me.

        • 1. Re: Date to Timestamp
          select to_timestamp(trunc(sysdate)) from dual
          • 2. Re: Date to Timestamp
            Date type may also have a time component, just like timestamp. On the other hand only timestamp types can store fractional seconds.

            So if you need to concatenate the hour/minute part for each date with a constant then you could even do it with the date data type.

            Anyway, to achieve what you're aiming this should do:
            Connected to Oracle Database 10g Enterprise Edition Release 
            Connected as fsitja
            SQL> create table messages (dt date);
            Table created
            SQL> create table sap_messages (dt timestamp);
            Table created
            SQL> insert into messages values (to_date('01/01/2010', 'DD/MM/YYYY'));
            1 row inserted
            SQL> insert into sap_messages
              2  select to_timestamp(to_char(dt, 'DD/MM/YYYY') || ' 12:00', 'DD/MM/YYYY HH24:MI')
              3    from messages;
            1 row inserted
            SQL> select * from sap_messages;
            01/01/10 12:00:00,000000
            Just be aware that if any of those dates already has an hour/minute, it will be lost and replaced with 12:00.
            • 3. Re: Date to Timestamp
              Frank Kulash

              CAST might be more robust and more efficient than TO_TIMESTAMP (TO_CHAR ...).
              Assuming the DATE column in messages is called d and the timestamp column in sap_messages is called ts:
              INSERT INTO  sap_messages (ts,                    ...)
                     SELECT             CAST (d AS TIMESTAMP), ...
                     FROM             messages
              This will keep the hours, minutes and seconds of d.
              If you want all the values of ts to be 12:00:00 AM, regardless of what time was in d, the use TRUNC (d) instead of d:
              INSERT INTO  sap_messages (ts,                            ...)
                     SELECT             CAST (TRUNC (d) AS TIMESTAMP), ...
                     FROM             messages
              1 person found this helpful
              • 4. Re: Date to Timestamp
                Hi Frank,

                Cast is best solution to get the actual timestamp value instead of just concateneting 12:00AM to all the timestamp values.

                thanks for your timely help.

                To other users,

                Thanks for stepping in and helping me, your solutions are also working but CAST is the robut one.

                thanks to everyone,