SQL "TO_DATE" fuction seems weird

Question

Quick question about the function "TO_DATE" in SQL_DEVELOPER who looks weird.

I need to do an SQL query to make an dashboard after and for that, I need to change date to be more understandier for other people.

So i've got a column called:

DT_EXP_C (EXPIRATION DATE) | CHAR (8 BYTE)

So I try this:

SELECT

dt_exp_c AS DATE_TEST -- RAW DATA,

TO_DATE (dt_exp_c, 'YYYYMMDD') AS EXPIRATION_DATE -- WORKED DATA

FROM et_co

The return of this query is:

DATE_TEST	EXPIRATION_DATE
20470130	30/01/47
20480810	10/08/48
...	...

The problem is, I want 'YYYY' to be for example '2048' but even if i put 'YYYY' in the code, the code return only the two last figures... ( '48' instead of '2048')

If you could help me to resolve this thing, it would be really nice!

While waiting to your answer i'll keep looking for a solution by myself :)

Good day :)

Answer 1

In Oracle, a DATE data type 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.

In the client application, SQL Developer, the client will receive the unformatted binary DATE from the database and will try to display it to you, the user, in a meaningful way. The default method is to convert the date to a string using the NLS_DATE_FORMAT session parameter; so what you are seeing is not a date but the client application's string representation of the date for display purposes.

In this case, the NLS_DATE_FORMAT for your territory is set to DD/MM/RR (see Oracle's default date format).

If you want to change it then either set the preferences in SQL Developer (which will set the NLS_DATE_FORMAT session parameters for this session and then automatically when you log into each subsequent session) or use:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

(or whatever format you want the dates to be displayed as.)

To set the format in your current session.

---

I need to do an SQL query to make an dashboard after and for that, I need to change date to be more understandier for other people.

Then do not rely on implicit formatting of dates, convert the date back to a string with your desired formatting model:

SELECT dt_exp_c AS DATE_TEST,
       TO_CHAR(
         TO_DATE (dt_exp_c, 'YYYYMMDD'),
         'YYYY-MM-DD HH24:MI:SS'         -- Or whatever format model you require
       ) AS EXPIRATION_DATE
FROM et_co

If you do not provide an explicit format then the method of displaying the binary DATE value is left up to the whims of whatever client application you use to connect to the database.