How to insert date and time in Oracle databases?

Question

I have a column in my TRANSACTION table called TxDate and another called TxTime. I am having trouble understanding how the TO_DATE function works. From the research I have done the following statement looks like it is correct. Can someone please help me out in understanding how to add a date and time in Oracle. Cheers
INSERT INTO TRANSACTION 
VALUES(
    TxNbr_Seq.nextval, 
    'X', 
    1000001, 
    123.45, 
    TO_DATE('2019/05/1', 'yyyy/mm/dd'), 
    TO_DATE('12:00', 'HR:MI'), 
    '101'
);

GMB · Answer

Don't store the date and time components in separate tables.
First, Oracle does not have a time datatype: it just has the date datatype, that stores both the date and time components.
Second, it is usually inefficient to proceed that way. Sooner or later, you end up concatenating the date and time components to be able to perform proper date comparison. It is far simpler to store both together, and use date functions if you need to display them separately.
So, just have a single column to store date and time, say txDate, and then:
INSERT INTO TRANSACTION -- better enumerate the target columns here
VALUES(
    TxNbr_Seq.nextval, 
    'X', 
    1000001, 
    123.45, 
    TO_DATE('2019/05/01 12:00', 'yyyy/mm/dd hh24:mi'),
    '101'
);

Note that Oracle uses hh24 (or hh12, or hh if you use AM/PM format) to specify the hours format rather than hr.
Then if you need to display the date and time separately, you can use date formatting functions:
to_char(txDate, 'yyyy/mm/dd') txDay,
to_char(txDate, 'hh24:mi'   ) txTime

How to insert date and time in Oracle databases?

One Answer

Add your own answers!

Ask a Question