sql – How to convert this date to datetime in oracle?

Question:

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

Some failed attempts resulting 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 doesn't:

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

Answer:

I don't know if I understand, see if that's 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 show milliseconds:

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

FROM DUAL;

Com timestamp:

select to_char(systimestamp,'dd-mm-yyyy hh24:mi:ss.FF') as ts from dual;
Scroll to Top