CODEWITHSUNDEEP
sqloracle-database
Moudiz
Moudiz

Reputation: 7377

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

Upvotes: 0

Views: 1501

Answers (3)

Ubercool
Ubercool

Reputation: 1021

SYSDATE always contains time information in oracle. If you are using sql developer try changing date format settings in preferences as suggested in this post

Upvotes: 1

Michael Piankov
Michael Piankov

Reputation: 1997

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;

Upvotes: 4

JohnHC
JohnHC

Reputation: 11195

Try something simpler...

insert into _LOGTB (PROCESS_NAME,TYPE,START_DATE,END_DATE,NUM_OF_COUNT)
  values 
    ('PROCEDUREEEE', 'INSERT', to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS'), '', 132111)

Upvotes: 1

Related Questions