how to convert 2022/02/01 12:56:15 this into 1-feb-2022 IN ORACLE

Question

I have start_tsp as value 2022/02/01 12:56:15 and it's datatype is varchar and i want to convert this into 1-FEB-22 in oracle. so, wt will be the syntax for this one.

SELECT to_date(start_tsp ,'YYYY/MM/DD')
FROM ETL_CONTROL.ETL_PARAMETERS 

I tried this one but it getting error

'ORA-01858: a non-numeric character was found where a numeric was expected'

Answer 1

First you'll have to convert it to DATE, and then back to CHAR using appropriate format mask:

SQL> alter session set nls_date_language = 'english';

Session altered.

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select    to_date('2022/02/01 12:56:15', 'yyyy/mm/dd hh24:mi:ss')                 as date_value,
  2    to_char(to_date('2022/02/01 12:56:15', 'yyyy/mm/dd hh24:mi:ss'), 'dd-mon-yyyy') as char_value
  3  from dual;

DATE_VALUE          CHAR_VALUE
------------------- -----------
01.02.2022 12:56:15 01-feb-2022

SQL>

Answer 2

You simply have to convert it into date and then convert into to char to get desired result.

           SELECT 
           to_char(to_date(start_tsp  , 'yyyy/mm/dd hh24:mi:ss'),'DD-MON-YY') dt 
           FROM ETL_CONTROL.ETL_PARAMETERS ;