Moudiz Moudiz - 1 month ago 9
SQL Question

Variable that stores date with minutes and seconds

I want to add time with minutes and seconds in a log table, I was using SYSDATE but it doesn't store minutes and seconds:

SYS_DATE TIMESTAMP;

SYS_DATE :=TO_DATE(''||SYSTIMESTAMP()||'','DD-MM-YYYY HH24:MI:SS');

INSERT INTO _LOGTB(PROCESS_NAME,TYPE,START_DATE,END_DATE,NUM_OF_COUNT) VALUES
(''PROCEDUREEEE'',''INSERT'','''||SYS_DATE||''','''','132111')';
commit;


I get this error:


ORA-01830: date format picture ends before converting entire input string


The output if I use
sysdate()


enter image description here

Answer

I'm sorry but its not true. Sysdate contain minutes and seconds.May be you don't see it because of your datetime system format.

Next

As I see you use something like dynamic sql. And may be error in another place.

Next

If you use datatype timestamp you don't need to convert systimestamp to timestamp. It's already done.Furthermore its strange convert it to date. Please just use next if you need timestamp.

SYS_DATE := SYSTIMESTAMP();

My next assumption is your table _LOGTB contain START_DATE,END_DATE columns with date - datatype. If so you may insert sysdate and all be ok.

 INSERT INTO _LOGTB(PROCESS_NAME,TYPE,START_DATE,END_DATE,NUM_OF_COUNT) VALUES
('PROCEDUREEEE','INSERT',SYSDATE,null,132111);  

And at least. You may check are your table contains information about minutes and seconds with next query:

select to_char(START_DATE,'YYYY-MM-DD HH24:MI:SS') from _LOGTB; 
Comments