How to add current hour and minutes to inserted date-CodePudding

I'm inserting data into table with date value, how to add to inserting date value current system time in format HH:MM

My insert

INSERT INTO tab1 VALUES to_date('11-OCT-2021');

I wanna insert '11-OCT-2021 22:08' where 22:08 current system time. How to do it

Thx

CodePudding user response：

You can use:

INSERT INTO tab1 (column_name)
  VALUES ( DATE '2021-10-11'   (SYSDATE - TRUNC(SYSDATE)) );

INSERT INTO tab1 (column_name)
VALUES (
  TO_DATE(
    '11-OCT-2021' || TO_CHAR(SYSDATE, 'HH24:MI:SS'),
    'DD-MON-YYYYHH24:MI:SS',
    'NLS_DATE_LANGUAGE=American'
  )
);

db<>fiddle here

CodePudding user response：

Here's one option:

SQL> create table tab1 (datum date);

Table created.

SQL> insert into tab1 values (date '2021-10-11');

1 row created.

SQL> select * from tab1;

DATUM
-------------------
11.10.2021 00:00:00

SQL> update tab1 set datum = to_date(to_char(datum, 'dd.mm.yyyy') || to_char(sysdate, 'hh24:mi'), 'dd.mm.yyyy hh24:mi');

1 row updated.

SQL> select * from tab1;

DATUM
-------------------
11.10.2021 21:47:00

SQL>

On the other hand, why wouldn't you insert the "whole" value immediately?

SQL> rollback;

Rollback complete.

SQL> insert into tab1 values (sysdate);

1 row created.

SQL> select * from tab1;

DATUM
-------------------
12.10.2021 21:48:21

SQL>