Insert date and time into table as (sysdate+1) with specific time

Question

I want to insert data and time into a SQL database field as (sysdate+1) with specific time.

Example: If current date/time is 05/03/2023 20:05:05 , the data/time should be inserted as 06/03/2023 00:30:00. So sysdate+1 for fixed time 00:30:00.

Is there any way to do that?

Database: Oracle9i Enterprise Edition Release 9.2.0.8.0 PL/SQL Release 9.2.0.8.0

update table set dvalid = trunc(sysdate+1);

This gives the (sysdate+1), but I want to set the time to exactly 00:30:00.

to_date('06-03-2023 00:30:00', 'dd-mm-yyyy hh24:mi:ss'); --> format

Answer 1

Try this:

update table set dvalid = (trunc(sysdate+1)+1/48);

This will work for the DATE data type, which tracks a full day as a differential of "1". An hour interval is therefore "1/24" and a 30 minute interval is "1/48". This will not work for a TIMESTAMP data type, which uses a different internal format and requires that you use an interval modifier:

(trunc(systimestamp) + interval '1' day + interval '30' minute)