Combine Different Date and Time Column to make one Column with Date/Time format in Oracle SQL

user_16105765 0
Viewed 100

I am working with database in which I have appointment table. With Two columns

ApptDate ApptTime
9/26/21  9:00 AM
9/25/20  1:00 PM

I want to drop the ApptTime column after making ApptDate Columns as ApptDateTime column. I have tried concatnate but I can't figure out, how can I change datatype of AppDate column to DATETIME data type and update all the values simultaneously. I tried following:

ALTER SESSION SET nls_date_format = 'DD-MON-YYYY hh24:mi'

UPDATE Appointment
SET ApptDate = ApptDate ||' ' ||ApptTime;

ALTER TABLE Appointment
MODIFY(
ApptDate DATE
);

But I got error that Data type can be changed of only Empty Table. Kindly suggest.

oracle-sqldeveloper sqldatatypes oracle
1 Answers
  • Case 1 : If ApptDate column is of string type, then recreate and populate your table as
/*CREATE TABLE Appointment( ApptDate VARCHAR2(15), ApptTime VARCHAR2(15) );

INSERT INTO Appointment
SELECT '9/26/21','9:00 AM' FROM dual UNION ALL
SELECT '9/25/20','1:00 PM' FROM dual; -- already existing state */

CREATE TABLE Appointment2 AS
SELECT TO_DATE(ApptDate||' '||ApptTime,'MM/DD/RR HH:MI PM') AS ApptDate      
  FROM Appointment;
  
DROP TABLE Appointment;

RENAME Appointment2 TO Appointment
  • Case 2 : If data type of ApptDate column is date, then use the following code block
/*CREATE TABLE Appointment( ApptDate DATE, ApptTime VARCHAR2(15) );

INSERT INTO Appointment
SELECT date'2021-09-26','9:00 AM' FROM dual UNION ALL
SELECT date'2020-09-25','1:00 PM' FROM dual; -- already existing state */

CREATE TABLE Appointment2 AS
SELECT TO_DATE(ApptDate||' '||ApptTime,'RRRR-MM-DD HH:MI PM') AS ApptDate      
  FROM Appointment;
  
DROP TABLE Appointment;

RENAME Appointment2 TO Appointment

Demo

user_58413060
Related