Forum Stats

  • 3,814,234 Users
  • 2,258,847 Discussions


Convert VARCHAR2 to Date format Oracle 11g

User_OPD8R Member Posts: 15 Green Ribbon


I want to convert a columns that its format is VARCHAR 2 to Date format

The columns is like this:

csv_stats_gen_time - column name

type: Varchar2

Example value: 2021-05-05 17:15:15:489

So i am using this query to convert it:

 SELECT TO_CHAR(TO_DATE(csv_stats_gen_time,'YYYY-MM-DD hh:mi:ss:mis'), 'YYYY-MM-DD') 

 FROM table_name

But it promts the error: ORA-01810: format code appears twice

Please can you support me on converting this column to 'YYYY-MM-DD' format?




  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Dec 6, 2021 4:31PM

    You say you want to convert it from a string (varchar) to a date. So why, after trying to convert it to a date (using to_date) do you then convert it back to a string (using to_char)?

    Your sample input string seems to represent the date/time down to milliseconds, but a DATE type only goes down to seconds.

    In your call to to_date, at least in your sample, you show an 'hour' value of '17'. But your format mask uses the hour element of 'hh', which is the 12-hour format. If your input data is using the 24-hour clock, then your format element should be 'hh24'.

    You use this date format with your call to to_date: 'YYYY-MM-DD hh:mi:ss:mis'. I don't see 'mis' documented as a legal format element. And since the DATE type only goes to seconds, why should TO_DATE accept any format describing fractions of seconds? If you disagree, please cite your source. MY source is the Release 18 SQL Language Reference.

  • User_OPD8R
    User_OPD8R Member Posts: 15 Green Ribbon

    Hello EdStevens

    Thanks on your reply.

    I just tried with this :

    select to_date(csv_stats_gen_time,'YYYY-MM-DD hh24:mi:ss','YYYY-MM-DD') from table_name

    And now i get this ORA-12702: invalid NLS parameter string used in SQL function

    Should i use any substr function here or anything else?

    Thanks in advance!


  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown

    You provided TWO date format masks in your call to to_date. That is patently wrong. What did you expect to accomplish with that?

    What is the end-point you are trying to achieve? Are you trying, as you said, to convert your varchar column to a DATE? Or are you trying to convert it to another string with a different format to represent the date that is represented by the starting string?

    What is it you really want to do with the end result?

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    SQL> -- -------- create the tables
    SQL> create table my_demo (string_date varchar2(25)
     2                      );
    Table created.
    SQL> -- -------- load tables
    SQL> insert into my_demo values ('2021-05-05 17:15:15:489');
    1 row created.
    SQL> commit;
    Commit complete.
    SQL> -- -------- Do the query
    SQL> -- check the starting string value
    SQL> select string_date from my_demo;
    2021-05-05 17:15:15:489
    1 row selected.
    SQL> -- use substring to cut off the decimal seconds
    SQL> select substr(string_date,0,19) from my_demo;
    2021-05-05 17:15:15
    1 row selected.
    SQL> -- convert the result to a DATE. Note that sqlplus will
    SQL> -- have to do an implied TO_CHAR in order to display a DATE type, using the default NLS_DATE_FORMAT
    SQL> select to_date(substr(string_date,0,19),'yyyy-mm-dd hh24:mi:ss') from my_demo;
    1 row selected.
    SQL> -- use to_char to convert the DATE back to a string in the desired format:
    SQL> select to_char(to_date(substr(string_date,0,19),'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd') fmt1,
     2          to_char(to_date(substr(string_date,0,19),'yyyy-mm-dd hh24:mi:ss'),'dd-Mon-yyyy') fmt2,
     3          to_char(to_date(substr(string_date,0,19),'yyyy-mm-dd hh24:mi:ss'),'DAY hh24:mi:ss') fmt3
     4 from my_demo;
    FMT1       FMT2                 FMT3
    ---------- -------------------- ---------------------------------------------
    2021-05-05 05-May-2021          WEDNESDAY 17:15:15
    1 row selected.
    SQL> -- -------- clean up
    SQL> drop table my_demo purge;
    Table dropped.

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown

    BTW, I just noticed that this thread is in the RMAN space. It has nothing to do with RMAN, nothing to do with recovering a database from backup.