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?

to_date is one way. but if you want the data to come in as mm/dd/yyyy then you will have to convert it to_char its not possible without it. — JT4U, Mar 20 '23 at 17:04
Dates have an internal 7-byte representation, they don't have any intrinsic format. You format them for display, as a string, either implicitly or explicitly; or your client does that for you, usually using your NLS settings. What are you really trying to achieve? — Alex Poole, Mar 20 '23 at 17:05
you need to show papf.start_date data type. Please tell me you are not storing a date as a VARCHAR2. — OldProgrammer, Mar 20 '23 at 17:10
no its a date, i just want to format it without converting it to char, anyway, a solution i have that is easier is to format the date on the ```rtf``` template itself, it worked — Aasem Shoshari, Mar 20 '23 at 17:33

MT0 · Answer 1 · 2023-03-20T19:47:06.420

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:

Explicitly convert a date to a string using TO_CHAR:

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

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.

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.

score 1 · Answer 2 · answered Mar 20 '23 at 17:51

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.

Oracle Format Date without Converting it to CHAR

2 Answers2