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.
>Solution :
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>