How to convert string datetime to string using SQL Oracle?

Question

In input I have a string datetime (yyyy-mm-dd HH:MI:SS), how can I convert it to HH:MI:SS dd/mm/yyyy in output?

Please clarify, as this is often a point of confusion. What is the actual data type of your "string datetime"? is it really a 'string" (varchar or varchar2) or is it of datatype DATE? If it is DATE, then you would use @Gordon Linoff answer. If it is truely a string, then you would simply use SUBSTR. — EdStevens
– EdStevens, Commented Jun 5, 2020 at 11:39

Popeye · Accepted Answer · 2020-06-05 12:18:39Z

1

You need to convert your string --> date --> string to achieve it as follows:

 -- consider, your string is - '2020-06-05 17:48:00'

SQL> SELECT TO_CHAR(
  2  TO_DATE('2020-06-05 17:48:00',
  3  'yyyy-mm-dd HH24:MI:SS'),
  4  'HH24:MI:SS dd/mm/yyyy'
  5  ) AS RES
  6    FROM DUAL;

RES
-------------------
17:48:00 05/06/2020

SQL>

answered Jun 5, 2020 at 12:18

Popeye

36k4 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

EdStevens Over a year ago

if the input truly is a string, then there is no need to convert it to a date, just to extract the "time" part of the string. All it would need would be "substr('2020-06-05 17:48:00',12)"

Popeye Over a year ago

But requirement is to format the date in different order.

Gordon Linoff · Accepted Answer · 2020-06-05 10:56:55Z

0

You use to_char():

 select to_char(col, 'HH24:MI:SS DD/MM/YYYY')

answered Jun 5, 2020 at 10:56

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

How to convert string datetime to string using SQL Oracle?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related