How to insert timestamp in Oracle?

Question

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

Answer 1

 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, follow these steps:

 insert into tablename (timestamp_value) values (CURRENT_TIMESTAMP); 

Answer 2

 INSERT INTO mytable (timestamp_field) VALUES (CURRENT_TIMESTAMP) 

CURRENT_TIMESTAMP and SYSTIMESTAMP are Oracle reserved words for this purpose. They are analogous to the SYSDATE .

Answer 3

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

Answer 4

The type depends on where the value you want to paste comes from. If you want to insert the current time, you can use CURRENT_TIMESTAMP as shown in other answers (or SYSTIMESTAMP ).

If you have 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') 

I hope that the components of the time format speak for themselves, except that FF3 means 3 digits accurate to the second. You can achieve 6 digits of accuracy.

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

Answer 5

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

Answer 6

Insert date in sql

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

If we want to insert a system date

 insert into tablename (timestamp_value) values (sysdate); 

Answer 7

First of all, you need to make the field Nullable, after which it is so simple - instead of putting the value, put this code CURRENT_TIMESTAMP .

Answer 8

For my future reference:

Using cx_Oracle use cursor.setinputsize (...):

 mycursor = connection.cursor(); mycursor.setinputsize( mytimestamp=cx_Oracle.TIMESTAMP ); params = { 'mytimestamp': timestampVar }; cusrsor.execute("INSERT INTO mytable (timestamp_field9 VALUES(:mytimestamp)", params); 

No conversion to db required. See Oracle Documentation

Answer 9

You can just use

 INSERT INTO MY_TABLE(MY_TIMESTAMP_FIELD) VALUES (TIMESTAMP '2019-02-15 13:22:11.871+02:00'); 

This way you don't have to worry about the date format string, just use the default timestamp format.

Works with Oracle 11, I have no idea if this is the case for earlier versions of Oracle.

Answer 10

 CREATE TABLE Table1 ( id int identity(1, 1) NOT NULL, Somecolmn varchar (5), LastChanged [timestamp] NOT NULL) 

this works for mssql 2012

 INSERT INTO Table1 VALUES('hello',DEFAULT)