How to output time instead of date in oracle?-CodePudding

I have created a table where startshift and endshift are supposed to output time but they get output as date. All of them are getting output as "1-JUL-22" How do i display 7:00AM for startshift and 3:00PM for endshift? This is my code:

CREATE TABLE EMPLOYEE(
EMPLOYEEID varchar(14) NOT NULL,
ROLEID varchar(5) NOT NULL,
BOOKINGID varchar(4) NOT NULL,
FIRSTNAME varchar(30) NOT NULL,
LASTNAME varchar(30) NOT NULL,
DOB date NOT NULL,
GENDER char NOT NULL,
PHONENUM number(8) NOT NULL,
EMAILADDRESS varchar(30),
STARTSHIFT date NOT NULL,
ENDSHIFT date NOT NULL,
CONSTRAINT EMPLOYEEID PRIMARY KEY (EMPLOYEEID)
);

The insert row codes are:

INSERT INTO EMPLOYEE VALUES ('0140581937202L', 'H010R', 4013, 'Eric', 'Luc', TO_DATE('14/05/1981', 'DD/MM/YYYY'),'M', 23058194844, '[email protected]', TO_DATE('7:00AM', 'HH:MI AM'), TO_DATE('3:00PM', 'HH:MI AM'));

INSERT INTO EMPLOYEE VALUES ('0250383009267M', 'D111W', 4015, 'Kneeny', 'Mireles', TO_DATE('25/03/1983', 'DD/MM/YYYY'),'M', 2302509213, '[email protected] ', TO_DATE('7:00AM', 'HH:MI AM'), TO_DATE('3:00PM', 'HH:MI AM'));

Can someone tell me how do i output the time? Do i have to change the datatype? I tried using TO_CHAR also

CodePudding user response：

Use TO_CHAR with the format model for the hours, minutes (and seconds):

SELECT TO_CHAR(date_column, 'HH24:MI:SS') AS time
FROM   table_name

or:

SELECT TO_CHAR(date_column, 'HH12:MIAM') AS time
FROM   table_name

So, for your table:

SELECT EMPLOYEEID,
       ROLEID,
       BOOKINGID,
       FIRSTNAME,
       LASTNAME,
       TO_CHAR(DOB, 'YYYY-MM-DD') AS dob,
       GENDER,
       PHONENUM,
       EMAILADDRESS,
       TO_CHAR(STARTSHIFT, 'HH12:MIAM') AS startshift,
       TO_CHAR(ENDSHIFT, 'HH12:MIAM') AS endshift
FROM    employee

CodePudding user response：

Two things.

Don't store phone numbers as numbers. Phone numbers have have a leading 0, you'll lost that. Use VARCHAR2. I had to cut off the area codes to get them to 'fit' in your table as-is.

And yes, use to_char() in your query to JUST get the TIME component of your date values.

select to_char(startshift, 'HH24:MI:SS') start_shift_time, to_char(endshift, 'HH24:MI:SS') end_shift_time from employee;