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.
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)
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);
INSERT INTO TABLE_NAME (TIMESTAMP_VALUE) VALUES (TO_TIMESTAMP('2014-07-02 06:14:00.742000000', 'YYYY-MM-DD HH24:MI:SS.FF'));
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
.
For my own future reference:
With 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 converting in the db needed. See Oracle Documentation
First of all you need to make the field Nullable, then after that so simple - instead of putting a value put this code CURRENT_TIMESTAMP
.
One can simply use
INSERT INTO MY_TABLE(MY_TIMESTAMP_FIELD)
VALUES (TIMESTAMP '2019-02-15 13:22:11.871+02:00');
This way you won't have to worry about date format string, just use default timestamp format.
Works with Oracle 11, have no idea if it does for earlier Oracle versions.
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
Source: Stackoverflow.com