Need to insert date column along with time and mins-CodePudding

I need to insert the date column record along with hour,min,sec, for example my joining date is 10/10/2021 but i need to insert 10/10/2021 08:05:25 i need output like this to get inserted

My insert query:

INSERT INTO lease_rent_receipts
(billing_start_date, billing_end_date,
charge_category, due_amount,
due_date, client_id,total_payable, description)
VALUES (l_billing_start_date,l_billing_end_date,l_charge_category, l_due_amount,
l_due_date, l_client_id,l_total_payable, l_description);

Main for due_date column i need to insert along with hrs:min:sec

kindly assist

CodePudding user response：

To demonstrate my earlier comments. Notice on the final query of the table, I am querying the same column - the same data - with three different formats:

SQL> create table my_demo
  2     (demo_date date)
  3  ;

Table created.

SQL> declare
  2   l_demo_date date := to_date('2021-10-22 12:21:43','yyyy-mm-dd hh24:mi:ss');
  3  begin
  4    insert into my_demo (demo_date)
  5          values (l_demo_date)
  6  ;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> select  demo_date,
  2          to_char(demo_date,'dd-Mon-yyyy') demo1,
  3          to_char(demo_date,'yyyy-mm-dd hh24:mi:ss') demo2
  4  from my_demo;

DEMO_DATE DEMO1                DEMO2
--------- -------------------- -------------------
22-OCT-21 22-Oct-2021          2021-10-22 12:21:43

1 row selected.

SQL> --
SQL> drop table my_demo purge;

Table dropped.

CodePudding user response：

Use timestamp

  to_timestamp(date_col, 'DD/MM/YYYY 24hh:mm:ss' )

CodePudding user response：

DECLARE
  l_billing_start_date lease_rent_receipts.billing_start_date%TYPE;
  l_billing_end_date   lease_rent_receipts.billing_end_date%TYPE;
  l_charge_category    lease_rent_receipts.charge_category%TYPE;
  l_due_amount         lease_rent_receipts.due_amount%TYPE;
  l_due_date           lease_rent_receipts.due_date%TYPE;
  l_client_id          lease_rent_receipts.client_id%TYPE;
  l_total_payable      lease_rent_receipts.total_payable%TYPE;
  l_description        lease_rent_receipts.description%TYPE;
BEGIN
  -- Use a DATE literal and add an INTERVAL DAY TO SECOND literal.
  billing_start_date := DATE '2021-10-10'   INTERVAL '08:05:25' HOUR TO SECOND;
  -- or use a TIMESTAMP literal.
  billing_start_date := TIMESTAMP '2021-10-10 08:05:25';
  -- Or use TO_DATE.
  billing_start_date := TO_DATE('10/10/2021 08:05:25', 'DD/MM/YYYY HH24:MI:SS');

  -- Set other variables.

  INSERT INTO lease_rent_receipts(
    billing_start_date, billing_end_date, charge_category, due_amount,
    due_date, client_id,total_payable, description
  ) VALUES (
    l_billing_start_date, l_billing_end_date, l_charge_category, l_due_amount,
    l_due_date, l_client_id,l_total_payable, l_description
  );
END;
/