sqloracle-databaseoracle-sqldeveloperto-dateto-timestamp

Oracle SQL to_date & to_timestamp ORA-01858: a non-numeric character was found where a numeric was expected & ORA-01850: hour must be between 0 and 23

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

Solution

  • 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