'How to convert this timestamp in PLSQL?

Using PL/SQL I have a TIMESTAMP(6) like this: 21-APR-22 02.25.00.000000 PM.

I need to convert it to format YYYY-MM-DD HH24:MI:SS (EX: 2022-04-21 14:25:00).

EDIT: I also need to store it as a VARCHAR

declare ts TIMESTAMP(6) :='21-APR-22 02.25.00.000000 PM'; 
dt VARCHAR(102); 
begin 
  dt:= TO_CHAR('19-APR-21 02.25.00.000000 PM','YYYY-MM-DD HH24:MI:SS'); 
  dbms_output.put_line(dt); 
  commit; 
END; 
[Error] Execution (50: 1): ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at line 5
sqlplsqlsql-timestamp


Solution 1:[1]

Use

TO_CHAR(ts, 'YYYY-MM-DD HH24:MI:SS')

with ts holding the timestamp value you want to turn into a varchar value matching to the desired format.

See this example (on dbfiddle.uk).

Solution 2:[2]

declare ts TIMESTAMP(6) :='21-APR-22 02.25.00.000000 PM'; 
dt VARCHAR(102); 
begin 
  dt:= TO_CHAR(ts,'YYYY-MM-DD HH24:MI:SS'); 
  dbms_output.put_line(dt); 
  commit; 
END;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 collapsar
Solution 2 Harsh Soni

Related Questions