oracle ora 01850,Oracle SQL to_date & to_timestamp ORA-01858: a non-numeric character was found wher...

问题

I have a small bit of code:

Code

SELECT to_date(it.DSTAMP, 'DD/MM/YYYY') AS "Date", to_timestamp(it.DSTAMP, 'HH24:MI:SS') AS Time

FROM itable it

Errors

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

01858. 00000 - "a non-numeric character was found where a numeric was expected"

*Cause: The input data to be converted using a date format model was

incorrect. The input data did not contain a number where a number was

required by the format model.

*Action: Fix the input data or the date format model to make sure the

elements match in number and type. Then retry the operation.

Error if I remove to_date

ORA-01850: hour must be between 0 and 23

01850. 00000 - "hour must be between 0 and 23"

*Cause:

*Action:

The DSTAMP field returns 24-SEP-14 08.55.33.997545000 without any formatting.

Obviously expected output is

24/09/2014 & 08:55:34

回答1:

It seems that it.DSTAMP is a TIMESTAMP

Replace to_date and to_timestamp with to_char

SELECT to_char(it.DSTAMP, 'DD/MM/YYYY') AS "Date", to_char(it.DSTAMP, 'HH24:MI:SS') AS Time

FROM itable it

来源：https://stackoverflow.com/questions/27425515/oracle-sql-to-date-to-timestamp-ora-01858-a-non-numeric-character-was-found-w