How to display all columns of the datatype DATE when one column is actually a date and others are just time?

Question

I am learning SQL and using Oracle SQL Developer. I have a table that contains the following columns

1. FlightDate
2. DepartureTime
3. ArrivalTime

I have inserted values using either

TO_DATE('10:45', 'hh24:mi')

or

TO_DATE('20/10/2000', 'DD/MM/YYYY')

When I do a SELECT * FROM TABLE_NAME, the DepartureTime and ArrivalTime display a date (which I have not entered). How do I display the date in the first column and time in the other 2 columns?

I have tried `

SELECT to_char(DepartureTime, 'HH24:MI' ) AS Departure
        to_char( ArrivalTime, 'HH24:MI' ) AS Arrival
  FROM FLIGHT;

` Although the above statement displays the right values, I want to write a statement to output all the columns (because the actual table has more than 3 columns), but in the format explained above - a date for FlightDate and time for DepartureTime and ArrivalTime.

Answer 1

In Oracle, a DATE is a binary data type that consists of 7 bytes representing century, year-of-century, month, day, hour, minute and second. It ALWAYS has all of those components and it is NEVER stored in any particular (human-readable) format.

Therefore, if you have a DATE it will always be both a date and a time.

---

If you only want to store the date component of a date then you will need to use the entire date but could add a constraint to ensure the time is always midnight.

If you want to store a time without a date then you can either:

1. Use a DATE data type and just set the time component (and ignore the default values of the date component); or
2. Use an INTERVAL DAY TO SECOND data type.

For example, your table could be:

CREATE TABLE table_name (
  FlightDate    DATE
                CONSTRAINT table_name__flightdate__chk CHECK (flightdate = TRUNC(flightdate)),
  DepartureTime INTERVAL DAY(0) TO SECOND(0) NOT NULL,
  ArrivalTime   INTERVAL DAY(1) TO SECOND(0) NOT NULL
);

Or, you could simplify your table to:

CREATE TABLE table_name (
  Departure DATE NOT NULL,
  Arrival   DATE NOT NULL
);

and not worry about having separate dates and times.

Answer 2

One option is to alter session's NLS and affect all DATE datatype columns at once. Here's how:

Sample table and some data:

SQL> create table flight
  2    (id             number,
  3     flight_date    date,
  4     departure_time date,
  5     arrival_time   date);

Table created.

SQL> insert into flight values (1, to_date('10:45', 'hh24:mi'), to_date('20/10/2000', 'dd/mm/yyyy'), sysdate);

1 row created.

This is what my database returns as a result:

SQL> select * From flight;

        ID FLIGHT_DA DEPARTURE ARRIVAL_T
---------- --------- --------- ---------
         1 01-NOV-22 20-OCT-00 24-NOV-22

Now, modify format to something else:

SQL> alter session set nls_date_Format = 'dd.mm.yyyy hh24:Mi';

Session altered.

Result:

SQL> select * From flight;

        ID FLIGHT_DATE      DEPARTURE_TIME   ARRIVAL_TIME
---------- ---------------- ---------------- ----------------
         1 01.11.2022 10:45 20.10.2000 00:00 24.11.2022 21:17

SQL>

---

This is a SQL*Plus example, but the same works in SQL Developer as well.

Alternatively, open SQL Developer's Preferences (in "Tools" menu), search for "NLS" and enter Date Format you want.

Answer 3

The question "When I do a SELECT * FROM TABLE_NAME, the DepartureTime and ArrivalTime display a date (which I have not entered). How do I display the date in the first column and time in the other 2 columns?" as I understand tells that DepartureTime and ArrivalTime columns are populated with date and time (as it is quite usual). I assume that your data looks something like here:

WITH
    flights (FLIGHT_OPERATOR, FLIGHT_FROM, FLIGHT_TO, DEPARTURE, ARRIVAL, SOME_OTHER_COL) AS
        (
            Select 'Vueling', 'Barcelona', 'Paris', To_Date('01.11.2022 10:45', 'dd.mm.yyyy hh24:mi'),   To_Date('01.11.2022 12:30', 'dd.mm.yyyy hh24:mi'), 'Some other stuff' From Dual Union All
            Select 'RyanAir', 'Barcelona', 'Dublin', To_Date('01.11.2022 11:10', 'dd.mm.yyyy hh24:mi'),   To_Date('01.11.2022 13:00', 'dd.mm.yyyy hh24:mi'), 'Some other stuff' From Dual Union All
            Select 'KLM', 'Barcelona', 'Amsterdam', To_Date('01.11.2022 20:10', 'dd.mm.yyyy hh24:mi'),   To_Date('01.11.2022 23:00', 'dd.mm.yyyy hh24:mi'), 'Some other stuff' From Dual Union All
            Select 'Lufthansa', 'Barcelona', 'Frankfurt', To_Date('01.11.2022 23:25', 'dd.mm.yyyy hh24:mi'),   To_Date('02.11.2022 02:20', 'dd.mm.yyyy hh24:mi'), 'Some other stuff' From Dual 
        )

Selecting all from table looks, probably, like below:

FLIGHT_OPERATOR	FLIGHT_FROM	FLIGHT_TO	DEPARTURE	ARRIVAL	SOME_OTHER_COL
Vueling	Barcelona	Paris	01-NOV-22	01-NOV-22	Some other stuff
RyanAir	Barcelona	Dublin	01-NOV-22	01-NOV-22	Some other stuff
KLM	Barcelona	Amsterdam	01-NOV-22	01-NOV-22	Some other stuff
Lufthansa	Barcelona	Frankfurt	01-NOV-22	02-NOV-22	Some other stuff

As the question was "How do I display the date in the first column and time in the other 2 columns?" I would say that you are looking for this:

Select 
    FLIGHT_OPERATOR, FLIGHT_FROM, FLIGHT_TO, 
    DEPARTURE, To_Char(DEPARTURE, 'hh24:mi') "DEPARTURE_TIME", 
    ARRIVAL, To_Char(ARRIVAL, 'hh24:mi') "ARRIVAL_TIME", 
    SOME_OTHER_COL
From
    flights
Order By 
    DEPARTURE, FLIGHT_OPERATOR

Result would be:

FLIGHT_OPERATOR	FLIGHT_FROM	FLIGHT_TO	DEPARTURE	DEPARTURE_TIME	ARRIVAL	ARRIVAL_TIME	SOME_OTHER_COL
Vueling	Barcelona	Paris	01-NOV-22	10:45	01-NOV-22	12:30	Some other stuff
RyanAir	Barcelona	Dublin	01-NOV-22	11:10	01-NOV-22	13:00	Some other stuff
KLM	Barcelona	Amsterdam	01-NOV-22	20:10	01-NOV-22	23:00	Some other stuff
Lufthansa	Barcelona	Frankfurt	01-NOV-22	23:25	02-NOV-22	02:20	Some other stuff

So, the Departure and Arrival columns containe both the date and the time. You can select whatever part of that DateTime structure you want. In this answer I didn't take any part for Departure and Arrival but the columns as a whole. Their display format is defined by NLS_DATE_FORMAT (you can format it using NLS or some other way of displaying date/time). I just extracted time parts for departure and arrival dates in separate columns (like in the question) as there could be some night flights with different dates of departure and arrival.
Regards...