CODEWITHSUNDEEP
sqloracle-databasedateto-char
Mr John
Mr John

Reputation: 241

Formatting timestamp

I have the below date in a table.

Table APEX

id     search_date
 1     04-OCT-18 08.36.12.000000 PM

I would like it to display as the below format. Is this possible?

id     search_date
 1     10/4/2018 8:36.12

Upvotes: 0

Views: 275

Answers (3)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59563

I order to suppress leading zeros you can use the FM switch:

to_char(search_date,'mm/FMdd/yyyy hh:mi.ss')

In case you like to suppress leading zeros also from month, it would be

to_char(search_date,'FMmm/dd/yyyy hh:mi.ss')

Note, FM acts as a switch, i.e. you turn off and on leading zeros and spaces.

For example 'FMmm/FMdd/yyyy hh:mi.ss' means: mm is truncated but dd/yyyy hh:mi.ss will not.

Upvotes: 2

Arulkumar
Arulkumar

Reputation: 13237

Using TO_CHAR() and TO_DATE():

It could be the partial answer, with out the .000000 PM in the input

SELECT TO_CHAR(TO_DATE('04-OCT-18 08.36.12','DD-MON-YY HH:MI:SS'), 'MM/DD/YYYY HH24:MI:SS') AS Result
FROM DUAL;

Result as 10/04/2018 08:36:12

UPDATE: Using TO_CHAR() and TO_TIMESTAMP()

SELECT TO_CHAR(TO_TIMESTAMP('04-OCT-18 08.36.12.000000 PM','DD-MON-YY HH:MI:SS.FF PM'), 'MM/DD/YYYY HH24:MI:SS') AS Result
FROM DUAL;

Demo on db<>fiddle, will result as 10/04/2018 20:36:12

Using this 04-OCT-18 08.36.12.000000 AM will result as 10/04/2018 08:36:12, demo for the same.

Upvotes: 0

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65393

You can use to_char, replace and ltrim string operator functions together :

select replace(to_char(search_date,'mm/dd/yyyy '),'/0','/')||
       ltrim(to_char(search_date,'hh:mi.ss'),'0') as search_date
  from apex;

SEARCH_DATE
-----------------
10/4/2018 8:36.12

Demo

Upvotes: 1

Related Questions