Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Convert Char To Number

Angus123Oct 26 2021

Hi
I have a column which is a CHAR(40).
It contains data like 20210930 or data like 2021-08-11 00:00:00.000 +02:00.
I want to convert to a number like 20210930 or 20210811
This works
SELECT TO_NUMBER(TO_CHAR(TO_DATE('20210930','YYYYMMDD'), 'YYYYMMDD')) FROM dual;
How do I convert it to a number like 20210811
I have tried
SELECT TO_NUMBER(TO_CHAR(TO_DATE('2021-08-11 00:00:00.000 +02:00','YYYYMMDD'), 'YYYYMMDD')) FROM dual;
and several variations but always seem to get an error
Ora-01843 Not a valid month
Regards
Gus

This post has been answered by Frank Kulash on Oct 26 2021
Jump to Answer

Comments

Post Details

Added on Oct 26 2021
3 comments
7,735 views