0

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Moudiz
  • 7,211
  • 22
  • 78
  • 156
  • Not sure what version of oracle you're using, but [sysdate](https://www.techonthenet.com/oracle/functions/sysdate.php) DOES include the current time – JohnHC Oct 25 '16 at 08:49
  • @JohnHC well i thought that too , i am using 11g maybe the vatiable was `date` so it cannot store sysdate into date ? ill update my question how I was storing the variable – Moudiz Oct 25 '16 at 08:52
  • is that double single qoutes? – Beginner Oct 25 '16 at 08:52
  • @NewbeeDev if i do it like this `SYS_DATE :=TO_DATE(SYSTIMESTAMP()','DD-MM-YYYY HH24:MI:SS');` I got the following error `ORA-01830: date format picture ends before converting entire input string` – Moudiz Oct 25 '16 at 08:55
  • 1
    ***NEVER***, **ever** call `to_date()` on a value that is *already* a `DATE`. Just don't. `TO_DATE(SYSTIMESTAMP()...` is a bug waiting to happen. There is no need to convert a `DATE` value to a `DATE` value. –  Oct 25 '16 at 09:05
  • 1
    A column defined as `DATE` **does** store minutes and seconds. If you don't see those values, your _SQL client_ doesn't display them. Change the configuration of your application _displaying_ those values. –  Oct 25 '16 at 09:07
  • @a_horse_with_no_name How to change them ? in this query `select NLS_DATE_FORMAT,NLS_TIME_FORMAT from nls_session_parameters` ? btw `select sysdate from dual` will give me the time with min and seconds , but assigning it in a variable will not give me the min and seconds. – Moudiz Oct 25 '16 at 09:14
  • Assigning a `DATE` variable with `sysdate` **will** preserve minutes and seconds. I have now idea how you display that variable and which tool you are using. But it **is** a configuration problem with your tool, not with the variable or the assignment. –  Oct 25 '16 at 09:16
  • @a_horse_with_no_name I want to adjust the configuration in my machine, I am using PL/SQL 7.1 and oracle 11g what kind of information you need from me ? so i can adjust them – Moudiz Oct 25 '16 at 09:18
  • There is no such thing as "PL/SQL 7.1". You need to tell us which _tool_ you are using. –  Oct 25 '16 at 09:20

3 Answers3

4

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;
Michael Piankov
  • 1,989
  • 1
  • 8
  • 19
  • what should be the format in my datetime systeme ? and I cannot insert the data like `value(...,sysdate..)` because I want to use the time somewhere else – Moudiz Oct 25 '16 at 09:04
  • date format: Sysdate its date and time now. In your query we see only date it means that your current nls_options in PL/SQL developer use format only with date or your nsl_date_format contain only 'dd/mm/yyyy'; You may try to change it in SQLPLUS – Michael Piankov Oct 25 '16 at 09:41
  • `alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'` and `select sysdate from dual` and after it `alter session set nls_date_format='yyyy-mm-dd'` and `select sysdate from dual` – Michael Piankov Oct 25 '16 at 09:42
1

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)
JohnHC
  • 10,935
  • 1
  • 24
  • 40
1

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

Community
  • 1
  • 1
Ubercool
  • 1,029
  • 2
  • 14
  • 29