Advertisement
Advertisement


How to insert a timestamp in Oracle?


Question

I have an Oracle DB with a timestamp field in it. What is the correct SQL code to insert a timestamp into this field?

2015/11/20
1
91
11/20/2015 6:21:46 AM

Accepted Answer

insert
into tablename (timestamp_value)
values (TO_TIMESTAMP(:ts_val, 'YYYY-MM-DD HH24:MI:SS'));

if you want the current time stamp to be inserted then:

insert
into tablename (timestamp_value)
values (CURRENT_TIMESTAMP);
2011/03/24
135
3/24/2011 2:52:17 PM

INSERT
INTO    mytable (timestamp_field)
VALUES  (CURRENT_TIMESTAMP)

CURRENT_TIMESTAMP and SYSTIMESTAMP are Oracle reserved words for this purpose. They are the timestamp analog of SYSDATE.

2015/05/12

INSERT INTO TABLE_NAME (TIMESTAMP_VALUE) VALUES (TO_TIMESTAMP('2014-07-02 06:14:00.742000000', 'YYYY-MM-DD HH24:MI:SS.FF'));
2014/07/31

Kind of depends on where the value you want to insert is coming from. If you want to insert the current time you can use CURRENT_TIMESTAMP as shown in other answers (or SYSTIMESTAMP).

If you have a time as a string and want to convert it to a timestamp, use an expression like

to_timestamp(:timestamp_as_string,'MM/DD/YYYY HH24:MI:SS.FF3')

The time format components are, I hope, self-explanatory, except that FF3 means 3 digits of sub-second precision. You can go as high as 6 digits of precision.

If you are inserting from an application, the best answer may depend on how the date/time value is stored in your language. For instance you can map certain Java objects directly to a TIMESTAMP column, but you need to understand the JDBC type mappings.

2019/05/09

I prefer ANSI timestamp literals:

insert into the_table 
  (the_timestamp_column)
values 
  (timestamp '2017-10-12 21:22:23');

More details in the manual: https://docs.oracle.com/database/121/SQLRF/sql_elements003.htm#SQLRF51062


Inserting date in sql

insert
into tablename (timestamp_value)
values ('dd-mm-yyyy hh-mm-ss AM');

If suppose we wanted to insert system date

insert
into tablename (timestamp_value)
values (sysdate);
2016/11/11