0

I am trying to convert a timestamp into date, but the following is not working.

select to_date('28-06-21 23:53:58.123', 'dd-MM-yy HH24:MI:SS.FF') from dual;

It says that the date format is not recognized. The problem is caused by the sub-seconds part, as the following works.

select to_date('28-06-21 23:53:58', 'dd-MM-yy HH24:MI:SS') from dual;

How can I fix this?

MetallicPriest
  • 29,191
  • 52
  • 200
  • 356
  • [For each DATE value, Oracle stores the following information: year, month, day, hour, minute, and second.](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Data-Types.html#GUID-5405B652-C30E-4F4F-9D33-9A4CB2110F1B) – astentx Jul 01 '21 at 13:42
  • 1
    to_date creates a DATE. A DATE does not include fractional seconds. Therefore, to_date data mask makes no provision for fractional seconds. TIMESTAMP does include fractional seconds. If you want to convert TIMESTAMP to DATE, you will have to drop the fractional seconds. – EdStevens Jul 01 '21 at 14:28
  • I solved it by using the following, SELECT TO_DATE(CAST(TO_TIMESTAMP('28-06-21 23:53:58.123', 'DD-MM-YY HH24:MI:SS.FF') AS DATE)) FROM dual – MetallicPriest Jul 01 '21 at 14:47
  • 1
    @MetallicPriest `to_date` converts a string to a date. Your `cast(... as date)` already returns a date, so you don't need to convert it again. In fact, using `to_date` on a date implicitly forces Oracle to first convert it to a string, but doesn't tell it how, so it has to rely on the `nls_date_format` and NLS language settings, which can have unexpected results. – William Robertson Jul 01 '21 at 16:53

0 Answers0