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.

CodePudding user response：

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.

CodePudding user response：

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 (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.