This command should insert a record whose one of fields is millisecond, which gets by a sub query, into a table but returns an error. What is the problem?
insert into table1 values ( select trunc( (to_date('2019-11-26 01:00:00', 'YYYY-MM-DD HH24:MI:SS') - to_date('1970-01-01', 'YYYY-MM-DD')) * power(60, 2) * 24 * power(10, 3)) from dual, 0, 0, 0);
Error report -
SQL Error:
ORA-00936: missing expression
00936. 00000 - "missing expression"
Please use below query,
Also you can Specify the columns names in the query
insert into table1(col1, col2, col3 ...) ( select trunc( (to_date('2019-11-26 01:00:00', 'YYYY-MM-DD HH24:MI:SS') - to_date('1970-01-01', 'YYYY-MM-DD')) * power(60, 2) * 24 * power(10, 3)),0, 0, 0 from dual);
You can just use insert . . . select:
insert into table1
select trunc( (to_date('2019-11-26 01:00:00', 'YYYY-MM-DD HH24:MI:SS') - to_date('1970-01-01', 'YYYY-MM-DD')) * power(60, 2) * 24 * power(10, 3)),
0, 0, 0
from dual;
The values keyword is not necessary. However, you should be including the list of columns for the table.
You don't need a SELECT and you should always specify the column for INSERT:
insert into table1 (col1, col2, col3, col4)
values (
trunc( (to_date('2019-11-26 01:00:00', 'YYYY-MM-DD HH24:MI:SS') - DATE '1970-01-01') * 60*60*24*1000), 0, 0, 0
);
Note, I assume you like to calculate a Javascript timestamp or similar. This is the number of Milliseconds since 1970-01-01 00:00:00 **UTC**. For precise values (i.e. if Milliseconds matters) and proper time zone handling you should use TIMESTAMP WITH TIME ZONE rather than DATE. For example like this:
TRUNC((TO_TIMESTAMP_TZ('2019-11-26 01:00:00.00 Europe/Zurich', 'YYYY-MM-DD HH24:MI:SS.FF TZR') - TIMESTAMP '1970-01-01 00:00:00 UTC') * 60*60*24*1000)
* 60*60*24*1000- would be simpler in my eyes.