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

Answer 1

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.

Answer 2

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')