How can I replace date in oracle systimestamp

Question

I can only use specific basedate value(YYYYMMDD) at my query.

I'd like to insert data with timestamp but just replaced with that date

INSERT INTO table (field1, field2, regTs)
 VALUES (:field1, :field2, systimestamp);

from this query, how can I apply :basedate + systimestamp's hour, minute, second, microsecond

seems duplicate. see the answer `[link](https://stackoverflow.com/questions/32939206/in-oracle-sql-how-do-you-insert-the-current-date-time-into-a-table)1 — MKR, Jul 08 '17 at 05:35
Please add some sample date values that the application will insert and what you want to have inserted into the table. For example, if the application inserts 20170708, what would you want to see if it were queried with select * from table? — Jeff Holt, Jul 08 '17 at 07:06
@jeff6times7 if today is 20170710 and current time is 18:46:14.2341 and :basedate is 20160502 then inserted data would be 2016-05-02 18:46:14.2341 I wanna use :basedate and current systimestamp time. The answer bellow is what I wanted. But I wanna find better way — abend0, Jul 10 '17 at 09:47

score 0 · Answer 1 · answered Jul 08 '17 at 10:21

0

First convert date to string using To_Char and concatenate time string with date string, now you can use To_Date function to convert the datetime string into timestamp.

Ex:

to_date(to_char(BASEDATE, 'YYYY/MM/DD') || ' ' ||TO_CHAR(sysdate,'HH24:MI:SS.FF'), 'YYYY/MM/DD HH24:MI:SS.FF')

Hope this helps.

answered Jul 08 '17 at 10:21

TAS

331
3
10

thx. But I'm worry about speed with this solution. thousands of transaction processed in few minute. Is this is the only way? – abend0 Jul 08 '17 at 10:30
@abend0 Try to do the concatenation in the scripting side if possible. And read the following article http://www.oracle.com/technetwork/issue-archive/2014/14-sep/o54asktom-2279184.html – TAS Jul 08 '17 at 10:58

score 0 · Answer 2 · answered Jul 10 '17 at 21:15

If you know :basedate is constant for a given user experience (execution of a task), then your first step should be to calculate the interval between sysdate and :basedate. Execute this once to figure it out and minimize CPU consumption later:

select numtodsinterval(sysdate - :basedate, 'DAY') dsinterval
from dual;

Then you can replace systimestamp in your insert statement text with this:

systimestamp - :dsinterval

If :basedate changes quite frequently, then you have to put more of the code into the expression, like this:

systimestamp - numtodsinterval(sysdate - :basedate, 'DAY')

Obviously, in the above examples, the datatype for :basedate is date. If it were a string datatype, then you would have to refer to it like this if the date string is formatted according to the current setting of NLS_DATE_FORMAT:

to_date(:basedate)

or this depending upon how the date string were formatted:

to_date(:basedate, 'some date format string')

How can I replace date in oracle systimestamp

2 Answers2