Changing datetime format in sql

August 24, 2023

I am trying to change the format of a datetime column from ‘YYYY-MM-DD HH-MM-SS’ to ‘YYYY-DD-MM HH-MM-SS’

input: 2023-04-12 07:15:10
script that is used in a select statement:

TO_CHAR(TO_DATE(a.time_stamp, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-DD-MM HH24:MI:SS')  as CHANGE_TIME

output: 0012-23-04 00:00:00
desired output: 2023-12-04 07:15:10

>Solution :

That’s because you applied to_date function to value that already is a date.

Sample data:

SQL> WITH
  2     test (time_stamp)
  3     AS
  4        (SELECT TO_DATE ('2023-04-12 07:15:10', 'yyyy-mm-dd hh24:mi:ss') FROM DUAL)
  5  --

Query; I believe you should use expression from line #7:

  6  SELECT TO_CHAR (TO_DATE (a.time_stamp, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-DD-MM HH24:MI:SS') you,
  7         TO_CHAR (time_stamp, 'yyyy-dd-mm hh:mi:ss') me
  8    FROM test a;

YOU                 ME
------------------- -------------------
0012-23-04 00:00:00 2023-12-04 07:15:10

SQL>