Oracle Format Date without Converting it to CHAR

Question

I Want to Format Date in Oracle without Converting to char e.g. to_char(date,'dd/mm/yyyy'), I tried using to_date:

to_date(papf.start_date,'yyyy-mm-dd')

But it doesn't really format anything, in addition I want the format to be mm/dd/yyyy, how can I do that?

Answer 1

If you don't want to specify a literal date format (which is in fact recommended practice), you can set the default for all date-to-string conversions:

alter session set nls_date_format='mm/dd/yyyy';

select start_date from ...

However, while this will work in sqlplus and any other client that converts everything to a string, should your client be actually wanting a date, no conversion will take place, and you wouldn't want it to. Work with dates as actual dates, not strings, until you get to the final point of displaying it to a human or on a report. If you are using a database browsing or reporting tool of some kind, it is likely that it has an Options section somewhere where you can set the default NLS date format automatically.

Answer 2

A DATE is a binary data type consisting of 7 bytes representing century, year-of-century, month, day, hour, minute and second. It ALWAYS has those 7 components and it is NEVER stored in any particular human-readable format.

Asking:

I Want to Format Date in Oracle without Converting to char

Does not make sense given that a date is stored as binary data. It is like asking to look at the bytes of an MP3 file without converting it to sound or to look at a JPEG file without decoding the bytes to pixels and colours.

If you want to have a date in a particular format then you need to convert it to a non-binary format and that would be a string of characters.

You can either:

1. Explicitly convert a date to a string using TO_CHAR:

   SELECT TO_CHAR(papf.start_date,'yyyy-mm-dd')
   FROM   your_table papf
   

2. Allow whatever client application you are using to access the database to implicitly format the date as a string when it displays it to you. If you are using SQL*Plus or SQL Developer then they will use the NLS_DATE_FORMAT session parameter as the default format model for implicitly converting between dates and strings and you can set that using:

   ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd';
   

   Then select the value as a date and allow the client to format it:

   SELECT papf.start_date
   FROM   your_table papf
   

   If you are using a different client application then you will need to look at its documentation and find out how it formats dates.

3. If you really don't want it as a string then you can use:

   SELECT TO_NUMBER(TO_CHAR(papf.start_date, 'DDMMYYYY'))
   FROM   your_table papf
   

   or

   SELECT 1000000 * EXTRACT(DAY   FROM papf.start_date)
          + 10000 * EXTRACT(MONTH FROM papf.start_date)
          +     1 * EXTRACT(YEAR  FROM papf.start_date)
   FROM   your_table papf
   

   Which both display it as an 8-digit number (but won't be in your desired format with the slashes).

---

I tried using to_date:

to_date(papf.start_date,'yyyy-mm-dd')

But it doesn't really format anything

It doesn't format anything because a DATE is a binary data-type and never stores any particular (human-readable) format.

Additionally, it can cause issues with the date as TO_DATE takes a string as the first argument so you are effectively performing an implicit date-to-string conversion and then converting it back to a date so the query is effectively the same as:

to_date(
  TO_CHAR(
    papf.start_date,
    (SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_DATE_FORMAT')
  ),
  'yyyy-mm-dd'
)

So, if you do:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-RR';

SELECT TO_CHAR(
          SYSDATE,
          'yyyy-mm-dd hh24:mi:ss'
       ) AS today,
       TO_CHAR(
          to_date(SYSDATE,'yyyy-mm-dd'),
          'yyyy-mm-dd hh24:mi:ss'
       ) AS today_with_to_date
FROM   DUAL;

Then it outputs:

TODAY	TODAY_WITH_TO_DATE
2023-03-20 18:42:40	0020-03-23 00:00:00

fiddle

In this case, using TO_DATE has swapped the year and day over and has truncated the time back to midnight.

NEVER use TO_DATE on a value that is already a date.