Oracle Data type convert

Question

There are two columns that have created which data type is Date, TimeStart and TimeEnd in my table (EventPlanLine). Well, the insert data is below shows:

Insert into EVENTPLANLINE (TIMESTART,TIMEEND) 
values (to_date('25-OCT-18 8:00:00','DD-MON-RR HH24:MI:SS'),
        to_date('25-OCT-18 17:00:00','DD-MON-RR HH24:MI:SS'));

Result:

25-OCT-18, 25-OCT-18

Forgive me about I only pick up those two columns data insertion to display. The result of the data presentation in the column shows DD-MON-RR format without time. Here is the question, how could the data presents with DD-MON-RR HH24:MI:SS format. I have checked from online resources, I could change NLS_DATE_FORMAT from Oracle, yes, it does and with time as below shows

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR HH24:MI:SS'

But the all tables which data type is Date have changed in the database. So is there a solution could change one table date type rather entire database?

You need to format it, when selecting: select to_char(timestart, 'dd-mon-rr') from .. — a_horse_with_no_name

Alex Poole Alex Poole · Accepted Answer · 2020-04-02T08:58:38

A date column does not have a human-readable format; there is an internal representation and the client decides how to display the results (usually based on NOS_DATE_FORMAT), unless you explicitly format a column in your query with to_char().

But the all tables which data type is Date have changed in the database

No, your client is using that setting to display the dates as formatted strings. Notign has changed in the database.

So is there a solution could change one table date type rather entire database?

No, you can't set a format for a column in a table - they are just dates.

All you can do is explicitly format as part of your query; each date can then be formatted differently. Rather contrived but to demonstrate:

alter session set nls_date_format = 'DD-Mon-RR';

select
  timestart,
  timeend,
  to_char(TIMESTART, 'YYYY-MM-DD HH24:MI:SS') as timestart_2,
  to_char(TIMEEND, 'FMDay DD/Mon/YYYY HH:MI AM', 'NLS_DATE_LANGUAGE=ENGLISH') as timeend_2
from EVENTPLANLINE;

TIMESTART TIMEEND   TIMESTART_2         TIMEEND_2                  
--------- --------- ------------------- ---------------------------
25-Oct-18 25-Oct-18 2018-10-25 08:00:00 Thursday 25/Oct/2018 5:0 PM

If the result format matters then don't rely on NLS formats as someone else running your code might have different settings in their session. (And as a related note, don't rely on implicit conversions of strings to dates - you aren't here but they often go together...)

Oracle Data type convert

1 Answers