Convert timestamp to date in Oracle 19c without time

Question

I'm coming from db2 and now need to cast a timestamp field to date in Oracle 19c.

Using cast(mytimestampfield as date) it is returning the time too. How to that without return the time?

Testing...it returns 2024-02-28 13:16:42

SELECT cast(SYSTIMESTAMP as date) FROM dual;

I'm looking for : 2024-02-28

Answer 1

In this case you can use truncate:

TRUNC(cast(SYSTIMESTAMP as date))

See the Oracle documentation for more information

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/TRUNC-date.html#GUID-BC82227A-2698-4EC8-8C1A-ABECC64B0E79

Answer 2

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

How to that without return the time?

Since a DATE always has those 7 components then it always has a time component.

If you want to set the time to midnight then use TRUNC:

SELECT SYSTIMESTAMP, TRUNC(SYSTIMESTAMP) FROM DUAL;

Outputs:

SYSTIMESTAMP	TRUNC(SYSTIMESTAMP)
2024-02-28 19:12:18.242271 +00:00	2024-02-28 00:00:00

If you want to display a date in the format YYYY-MM-DD then you can use TO_CHAR:

SELECT SYSTIMESTAMP, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD') FROM DUAL;

Which outputs the value as a string:

SYSTIMESTAMP	TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD')
2024-02-28 19:14:23.533246 +00:00	2024-02-28

fiddle