How to convert this date to datetime in oracle?

Question

How to convert this date to datetime in oracle?

Navigation

#1 by (2 votes)

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;

sql oracle

asked by anonymous 12.01.2017 / 17:02

1 answer

How to pass two variables from a view to the controller? Open div via ajax

score 2 · Accepted Answer

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;