需要插入日期列以及时间和分钟

Question

我需要插入日期列记录以及小时，分钟，秒，例如，我的加入日期是10/10/2021，但我需要插入10/10/2021 08:05:25我需要这样的输出来插入

我的插入查询:

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

主due_date列，我需要与hrs:min:sec一起插入

请协助

Answer 1

来演示我前面的注释。注意，在表的最后一次查询中，我用三种不同的格式查询同一列—相同的数据:

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.

Answer 2

使用时间戳

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

Answer 3

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;
/