0

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
Luuk
  • 12,245
  • 5
  • 22
  • 33
Schwab
  • 1
  • 1
  • 1
    Timestamps, dates are not persisted in a "format", so what you are showing is the display representation. in the client software you are using, which may be default formatted based on NLS parameters. If you want to show in a different format, then call to_char function on the timestamp data type with the format specifier you show. A [mcve] would be helpful – OldProgrammer Apr 22 '22 at 15:53
  • 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 – Schwab Apr 22 '22 at 16:00
  • Note that you do not need to specify the timestamp literal the way you do (which likely causes the pl/sql error). See the linked example in my answer, or consult the oracle docs. – collapsar Apr 22 '22 at 16:13
  • 3
    @Schwab: your code looks so much better in the question, then in the comments.... – Luuk Apr 22 '22 at 16:16
  • [Documentation page for literals](https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Literals.html) – William Robertson Apr 23 '22 at 09:18

2 Answers2

0

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).

collapsar
  • 17,010
  • 4
  • 35
  • 61
0
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;
Harsh Soni
  • 1
  • Remember that Stack Overflow isn't just intended to solve the immediate problem, but also to help future readers find solutions to similar problems, which requires understanding the underlying code. This is especially important for members of our community who are beginners, and not familiar with the syntax. Given that, **can you [edit] your answer to include an explanation of what you're doing** and why you believe it is the best approach? – Jeremy Caney Apr 30 '22 at 04:28