Need to insert date column along with time and mins

Question

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

You can use `to_date()` to convert a string variable to a date (including time - dates always have a time, even if it's midnight) using a suitable format mask; or you could cast a timestamp literal if you have a fixed value. But what data types are your `l_*` variables, and how are they supplied or assigned before you get to this statement? — Alex Poole, Oct 10 '21 at 12:26
We don't know the data types of billing_start_date, billing_end_date, l_billing_start_date, or l_billing_end_date. If the system is properly designed, they _should_ be of type DATE, which inherently contains both date _and_ time, down to the second. We also don't see what you see that makes you think they are not getting the time. It could very well be that your columns _do_ have the time, but your output is not formatted to show it as such. Remember, DATE (and TIMESTAMP) are binary structures of an internal format. — EdStevens, Oct 10 '21 at 17:37
.(.. cont) what you see on the screen is a character string representation of the internal binary DATE, and that can be formatted any way you designate. Please update your question to show the definitions of the elements, how the l_billing dates are getting populated, and what you see that makes you think you don't have the time. It would also help if you reformatted your code so that it is actually readable instead of a hot mess of run-on code. — EdStevens, Oct 10 '21 at 17:39

score 3 · Answer 1 · answered Oct 10 '21 at 17:58

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.

score 1 · Answer 2 · answered Oct 10 '21 at 12:30

1

Use timestamp

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

answered Oct 10 '21 at 12:30

Himanshu

3,830
2
10
29

2

Why *timestamp* and not *date*? DATE contains time up to seconds, and that's what the OP wanted; there's no need for fractional seconds (which is what TIMESTAMP contains). – Littlefoot Oct 10 '21 at 19:12

score 1 · Answer 3 · answered Oct 10 '21 at 20:10

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

Need to insert date column along with time and mins

3 Answers3