Can I hide a dd-mon-yyyy component from Oracle SQL DATE when I display the data and get only hh:mm:ss am format?

user_14748768 0
Viewed 55

I am inserting data into an SQL table. I needed to separate columns for date, and time. I defined it as:

CREATE TABLE Appointment (
    "Date" DATE,
    "Time" DATE);

and then I inserted values:

INSERT INTO Appointment VALUES ('10/Jan/2020', to_date('9:00:00 AM','hh:mi:ss am');
INSERT INTO Appointment VALUES ('15/Feb/2020', to_date('10:00:00 AM','hh:mi:ss am');

However, when I tried to display the values, all of the "Time" values were equal "1-Dec-2020", but the sorting based on the "Time" worked properly. Can I hide this "dd-mon-yyyy" from displaying, or remove it from the dataset?

This is how the output is displayed, I want to see my hh:mm:ss am format:

Screenshot showing Date and Time columns with DD-MMM-YY format

Unfortunately, I am working on Oracle SQL, so I cannot use TIME function.

datetime oracle sql
3 Answers

You can do this using strings and check constraints:

CREATE TABLE Appointment (
    "Date" DATE,
    "Time" VARCHAR2(8),
    CHECK chk_appointment_date (date = trunc(date)),
    CHECK chk_appointment_time (regexp_like(time, '^[0-2][0-9]-[0-1][0-9]-[0-5][0-9]')
);

Otherwise, just put the values in a single column and generate the date part values:

create table appointment (
    date_time date,
    the_date date generated always as (trunc(date_time)) virtual,
    the_time varchar(8) generated always (to_char(date_time, 'HH24:MI:SS'))
)
user_11440350

It is a very bad idea to store date/time as strings - don't do that!

Use proper DATE data type and format the output according to your needs (or use VIRTUAL COLUMN as proposed by Gordon):

CREATE TABLE Appointment ( DateTime DATE);

INSERT INTO Appointment VALUES 
   (to_date('10/Jan/2020 9:00:00 AM','dd/Mon/YYYY hh:mi:ss am', 'nls_date_language = american'));
INSERT INTO Appointment VALUES 
   (to_date('15/Feb/2020 10:00:00 AM','dd/Mon/YYYY hh:mi:ss am', 'nls_date_language = american'));

SELECT TO_CHAR(DateTime, 'hh:mi:ss am')
from Appointment;
user_30272660

Can I hide this "dd-mon-yyyy" from displaying

Yes, You can hide it. I would suggest not to use the TIME column alone, It is built-in with the date in oracle. but if this is required for you then You need to use the TO_CHAR as follows:

SELECT "Date", TO_CHAR("Time",'hh:mi:ss AM') from your_table;

I would have created the single column in the table with data type date and whenever needed, I can use TO_CHAR(DATECOLUMN, 'DD-MON-YYYY') for date and TO_CHAR(DATECOLUMN, 'hh:mi:ss AM') for time

user_115656290
Related