Getting date in oracle from timestamp without trailing o's?

Question

I have been scouring stack and google to find a way to extract a date from timestamp w/o the trailing zero's still being a date and NOT a VARCHAR variable-

for example:

SELECT TRUNC(to_date('2012-07-18 13:27:18', 'YYYY-MM-DD HH24:MI:SS')) FROM DUAL

gives: 18-JUL-12 00:00:00

I want it in '18/07/2012' date format in the end.

TIA in advance if this is possible.

Answer 1

Well, that's what you say. In my database, it results in

SQL> SELECT TRUNC(to_date('2012-07-18 13:27:18', 'YYYY-MM-DD HH24:MI:SS')) FROM DUAL;

TRUNC(TO
--------
18.07.12

SQL>

If you want to get a different result, alter session:

SQL> alter session set nls_Date_format = 'dd/mm/yyyy';

Session altered.

SQL> SELECT TRUNC(to_date('2012-07-18 13:27:18', 'YYYY-MM-DD HH24:MI:SS')) FROM DUAL;

TRUNC(TO_D
----------
18/07/2012

Or apply TO_CHAR with desired format mask:

SQL> SELECT to_char(TRUNC(to_date('2012-07-18 13:27:18', 'YYYY-MM-DD HH24:MI:SS')), 'dd/mm/yyyy') FROM DUAL;

TO_CHAR(TR
----------
18/07/2012

---

Although, I don't see much sense in what you are doing:

• first you convert a string ('2012-07-18 13:27:18') to date
• then you truncate time part of it
• finally, you want to get only date part, but as a string again

So, why don't you just use

SQL> select '18/07/2012' from dual;

'18/07/201
----------
18/07/2012

(Of course, unless that date string actually comes somewhere from a table which is wrong; never store dates into VARCHAR2 columns).

Answer 2

The data types DATE and TIMESTAMP are an oracle internal, binary type. BY DEFINITION they contain a time component - DATE resolved down to the seconds, TIMESTAMP down to the nano-second. Conversely, since these are internal, binary structures, what you see on your screen is, again BY DEFINITION, converted to a character string - VARCHAR2. You may not have specified the TO_CHAR, but the very act of selecting a DATE or TIMESTAMP to be displayed (not used internally) forced oracle to apply a TO_CHAR to it.

Since these data types inherently, by defintion, contain time components, it is logically inconsistent to say that you want to "extract a date from timestamp w/o the trailing zero's (the time component) still being a date and NOT a VARCHAR variable-".

Answer 3

I have been scouring stack and google to find a way to extract a date from timestamp w/o the trailing zero's still being a date and NOT a VARCHAR variable

You cannot if you want the value to still be a DATE

A DATE is stored internally as 7-bytes and always has year (2 bytes), month, day, hour, minute and second (1 byte each) components.

A TIMESTAMP is stored internally with 11-20 bytes with the same year-to-second components as a DATE but also fractional seconds components and optional time zone components.

Neither a DATE nor a TIMESTAMP has a format because they are just binary data and you cannot remove the time component because they both always have a time component.

SELECT TRUNC(to_date('2012-07-18 13:27:18', 'YYYY-MM-DD HH24:MI:SS')) FROM DUAL

gives: 18-JUL-12 00:00:00

The query outputs a DATE and depending on the user interface that you use then the date may be implicitly cast to a string for outputting. SQL/Plus and SQL Developer use the NLS_DATE_FORMAT session parameter to implicitly convert DATEs to strings when it is displaying it to the user:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT TRUNC(to_date('2012-07-18 13:27:18', 'YYYY-MM-DD HH24:MI:SS')) FROM DUAL;

Outputs: 2012-07-18 13:27:18

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
SELECT TRUNC(to_date('2012-07-18 13:27:18', 'YYYY-MM-DD HH24:MI:SS')) FROM DUAL;

Outputs: 2012-07-18

However, ANY user can change their own session parameters at ANY time so you should never rely on implicit formatting of dates.

If you want a DATE then you should not expect it to be formatted in any particular way as it is just binary data. If you want a formatted date then you should explicitly convert it to a string with the formatting you require using TO_CHAR:

SELECT TO_CHAR(
         TRUNC( to_date('2012-07-18 13:27:18', 'YYYY-MM-DD HH24:MI:SS') ),
         'DD/MM/YYYY'
       )
FROM   DUAL;

Outputs: 18/07/2012

db<>fiddle