How to convert this date to datetime in oracle?

3

Correct way to convert date '27/12/2016 16:31:39,137000000' to datetime (timestamp (6)) in oracle?

Some frustrated attempts that result in error ORA-01821: formato de data não reconhecido 01821. 00000 - "date format not recognized" :

  SELECT TO_CHAR(TO_DATE('27/12/2016 16:31:39,137000000', 'dd/mm/yyyy HH24:MI:SS'), 'DD/MM/YYYY') DT_C FROM DUAL;
  SELECT TO_CHAR(TO_DATE('27/12/2016 16:31:39,137000000', 'dd/mm/yyyy HH24:MI:SS.FF9'), 'DD/MM/YYYY') DT_C FROM DUAL;
  SELECT TO_CHAR(TO_DATE('27/12/2016 16:31:39,137000000', 'dd/mm/yyyy HH24:MI:SS.FF'), 'DD/MM/YYYY') DT_C FROM DUAL;

This works, but the date with the comma and the numbers after the seconds do not:

SELECT TO_CHAR(TO_DATE('27/12/2016 16:31:39', 'dd/mm/yyyy HH24:MI:SS'), 'DD/MM/YYYY') DT_C FROM DUAL;
    
asked by anonymous 12.01.2017 / 17:02

1 answer

2

I do not know if I understand, see if this is what you wanted:

SELECT TO_CHAR(TO_DATE('27/12/2016 16:31:39', 'dd/mm/yyyy HH24:MI:SS'), 'DD/MM/YYYY HH24:MI:SS') DT_C FROM DUAL;

To display the milliseconds:

SELECT TO_TIMESTAMP ('10-Sep-02 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF')

FROM DUAL;

With timestamp:

select to_char(systimestamp,'dd-mm-yyyy hh24:mi:ss.FF') as ts from dual;
    
12.01.2017 / 17:55