I have a table called transaction that contains a TxDateTime which is a DATE data type. When I run a query I would like to separate the date and time into their own columns; TxDate and TxTime. How can I achieve this??
Here is how I created the table and inserted the date/time:
Table creation
CREATE TABLE TRANSACTION(TxNbr INTEGER PRIMARY KEY,
TxCode CHAR(1) NOT NULL,
AccountNbr INTEGER NOT NULL,
Amount DECIMAL(13,2) NOT NULL,
TxDateTime DATE,
RefNbr VARCHAR(3),
FOREIGN KEY(AccountNbr) REFERENCES ACCOUNT (AccountNbr) ON DELETE SET NULL,
FOREIGN KEY(TxCode) REFERENCES TX_TYPE (TxCode) ON DELETE SET NULL
);
Insert into table
INSERT INTO TRANSACTION VALUES(TxNbr_Seq.nextval, 'X', 1000001, 123.45, TO_DATE('2019/05/01 12:00', 'yyyy/mm/dd hh24:mi'), '101');
Select
SELECT Transaction.TxDateTime,
FROM TRANSACTION
The output only gives the date: 19-05-01, but I would like the date and time in separate columns when I run a query.
Oracle doesn't have a time
data type. You can convert to a string:
select to_char(TxDateTime, 'YYYY-MM-DD') as date, to_char(TxDateTime, 'HH24:MI:SS')
from transaction t;