oracle sql

smalig
smalig used Ask the Experts™
on
Hello experts,
I need to insert a value that is from a to_date function to a timestamp column

ex: i have this select statement -- select TO_DATE(substr(M."LASTUPDATETIME",1,19),'YYYY-MM-DD HH24:MI:SS') from a

I need to insert this value to a timestamp(0) column.

the output from the select is --  2015-06-30 05:21:55

and when I insert it to the timestamp column it becomes --  0030-06-15 00:00:00


how can I fix this?
thanks for your help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>the output from the select is

In Oracle dates don't have a format until they are displayed.

You can insert a date into a timestamp column no problems:
drop table tab1 purge;
create table tab1(col1 timestamp);
insert into tab1 values(to_date('1/2/2003','MM/DD/YYYY'));

Please post your insert.

There is likely a string conversion error somewhere.
Mark GeerlingsDatabase Administrator

Commented:
I agree that this is most likely caused by an implicit datatype conversion, or an incorrect format mask somewhere if you use explicit datatype conversions.

What do these queries return in your system?

select sysdate from dual;

select systimestamp from dual;

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial