Timestamp to Date Conversion in Oracle-CodePudding

How to convert below Timestamp to Date?

The table has a column created_date with datatype Timestamp(6) and the sample value is 03-OCT-22 03.52.33.989000000 PM

I need a query that will convert the Timestamp into 'DD-MON-YYYY HH24:MI:SS' Ex: 03-OCT-2022 03:52:33 PM.

Please help me in writing this query.

CodePudding user response：

Just cast it:

SQL> create table test (created_date timestamp(6));

Table created.

SQL> insert into test values (to_timestamp('03.10.2022 03:52:33.989000', 'dd.mm.yyyy hh24:mi:ss.ff6'));

1 row created.

SQL> select cast(created_date as date) result from test;

RESULT
-------------------
03.10.2022 03:52:33

SQL>

If you want to reformat it, apply to_char with desired format model, e.g.

SQL> select to_char(created_date, 'dd-mon-yyyy hh:mi:ss pm', 'nls_date_language = english') result
  2  from test;

RESULT
-----------------------
03-oct-2022 03:52:33 AM

SQL>