This content has been marked as final. Show 4 replies
select to_timestamp(trunc(sysdate)) from dual
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>
Hi,1 person found this helpful
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 ;
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,