In Oracle SQL: How do you insert the current date + time into a table?

Question

I've written below code, but it only seems to insert the current date and not the current time. Anyone knows how to do that?

insert into errortable
(dateupdated,table1id)
values
(TO_DATE(sysdate, 'dd/mm/yyyy hh24:mi:ss'),1083);

How do you know that its only inserting current date?Did you try it by looking at the raw table or have you tried viewing it using a query? — brenners1302, Oct 05 '15 at 00:34
yeah it stores 00:00 as time ; i confirmed by looking in table. want to store current date and current time — Tikkaty, Oct 05 '15 at 01:50
Clue : In Oracle, Current Date + Time = Sysdate. `select sysdate from dual would produce 2015-10-05 09:24:40`. — Hotdin Gurning, Oct 05 '15 at 02:25
`TO_DATE(sysdate...)` is just wrong. `sysdate` returns a `date` - never ever call `TO_DATE` with a date because it will usually not give the results you might expect, and doesn't make sense anyway. — Jeffrey Kemp, Oct 05 '15 at 08:39

score 33 · Accepted Answer · answered Oct 04 '15 at 23:08

33

It only seems to because that is what it is printing out. But actually, you shouldn't write the logic this way. This is equivalent:

insert into errortable (dateupdated, table1id)
    values (sysdate, 1083);

It seems silly to convert the system date to a string just to convert it back to a date.

If you want to see the full date, then you can do:

select TO_CHAR(dateupdated, 'YYYY-MM-DD HH24:MI:SS'), table1id
from errortable;

answered Oct 04 '15 at 23:08

Gordon Linoff

1

thanks @gordon . are you saying sysdate insert should store the current date and current time using your method ? – Tikkaty Oct 05 '15 at 01:53
1

@Tikkaty . . . Yes, `sysdate` has a time component. – Gordon Linoff Oct 05 '15 at 02:10

score -2 · Answer 2 · edited Oct 05 '15 at 05:30

-2

You may try with below query :

INSERT INTO errortable (dateupdated,table1id)
VALUES (to_date(to_char(sysdate,'dd/mon/yyyy hh24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss' ),1083 );

To view the result of it:

SELECT to_char(hire_dateupdated, 'dd/mm/yyyy hh24:mi:ss') 
FROM errortable 
    WHERE table1id = 1083;

edited Oct 05 '15 at 05:30

Vishal

answered Oct 05 '15 at 02:04

brenners1302

4

it does not make sense to convert sysdate to a string and then back to a date – miracle173 Jan 23 '17 at 12:14

2 Answers2