Convert String to Date field for SQL Oracle

to_char(to_date( rtrim(new_value_02), 'yyyymmdd'), 'dd-mm-yyyy')

Should do the trick. rtrim removes spaces on right side of string. Then I convert it to date using the date format specified, and then convert it to a string again in the desired format.

Did tried to convert to date format and then to char again?

TO_CHAR(TO_DATE(PERSACTION.NEW_VALUE_02,'yyyymmdd'),'dd-mm-yyyy')

Please, please, please do not store DATEs and CHARACTER datatypes. This will only lead to issues that can be avoided when using the DATE datatype.

If you want to change the string 20170202 to another string and not actually a date (which would have no intrinsic formatted text representation), you could optionally use a regular expression to transform it, instead of converting to a date and back:

select regexp_replace('20170202     ', '^(\d{4})(\d{2})(\d{2}) +$', '\3-\2-\1')
from dual;

REGEXP_REPLACE(
---------------
02-02-2017     

Or you could use substr instead of regexp_substr, which may perform better even if you have to call it three times; using a CTE just to avoid repeating the value:

with t(str) as (
  select '20170202     ' from dual
)
select substr(str, 7, 2) ||'-'|| substr(str, 5, 2) ||'-'|| substr(str, 1, 4)
from t;

SUBSTR(STR
----------
02-02-2017

If you do convert to a date and back you would uncover any values which cannot be converted, as they will cause an exception to be thrown. That would imply you have bad data; which would have been avoided by using the right data type in the first place, of course. These will convert any old rubbish, with varying results depending on how far the strings stray from the pattern you expect - but including strings like '20170231' which represent an invalid date. And null value or strings of just spaces will be converted to odd things with the substr version, but you could filter those out.

You can see the kind of variation you would get with some sample data that doesn't match your expectations:

with t(str) as (
  select '20170202     ' from dual
  union all select '20170231     ' from dual
  union all select '2017020c     ' from dual
  union all select '2017020      ' from dual
  union all select '201702021    ' from dual
  union all select '             ' from dual
  union all select null from dual
)
select str,
  regexp_replace(str, '^(\d{4})(\d{2})(\d{2}) +$', '\3-\2-\1') as reg,
  substr(str, 7, 2) ||'-'|| substr(str, 5, 2) ||'-'|| substr(str, 1, 4) as sub
from t;

STR           REG           SUB          
------------- ------------- -------------
20170202      02-02-2017    02-02-2017   
20170231      31-02-2017    31-02-2017   
2017020c      2017020c      0c-02-2017   
2017020       2017020       0 -02-2017   
201702021     201702021     02-02-2017   
                              -  -       
                            --           

With the anchors and whitespace expectation, the regular expression doesn't modify anything that doesn't consist entirely of 8 numeric characters. But it can still form invalid 'dates'.