This content has been marked as final. Show 4 replies
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:
Just be aware that if any of those dates already has an hour/minute, it will be lost and replaced with 12:00.
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 Connected as fsitja SQL> 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; DT ------------------------------------------------- 01/01/10 12:00:00,000000 SQL>
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:
This will keep the hours, minutes and seconds of d.
INSERT INTO sap_messages (ts, ...) SELECT CAST (d AS TIMESTAMP), ... FROM messages ;
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 ;