CODEWITHSUNDEEP
oracle-database
Sushmitha
Sushmitha

Reputation: 1

selecting date value from to_timestamp in oracledb

I have a table of to_timestamp format. I want to select the date of format 06/06/2020 12:00:00:00 Am from that table

select * from table to_date('01-01-2015','DD-MM-YYYY');

can be done but I want to select in the format of 06/06/2020 12:00:00AM. How to write that query in Oracle?

Upvotes: 0

Views: 34

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

First you converted string to date (which is OK). Now you have to convert date back to string with appropriate format mask:

SQL> select to_char(to_date('01-01-2015','DD-MM-YYYY'), 'dd.mm.yyyy hh:mi:ssam') result
  2  from dual;

RESULT
---------------------
01.01.2015 12:00:00AM

SQL>

Though, you mentioned a "table" with "(to_?)timestamp" (there's no "to_timestamp" datatype, but "timestamp"). If it really is a table with timestamp datatype, then:

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:Mi:ss';

Session altered.

SQL> drop table test;

Table dropped.

SQL> create table test (col timestamp);

Table created.

SQL> insert into test values (systimestamp);

1 row created.

SQL> select col,
  2    cast(col as date) col_date,
  3    to_char(cast(col as date), 'dd.mm.yyyy hh:mi:ssam') result
  4  from test;

COL                            COL_DATE            RESULT
------------------------------ ------------------- ---------------------
22.06.2020 20:31:26.723000     22.06.2020 20:31:26 22.06.2020 08:31:26PM

SQL>

Upvotes: 1

Related Questions