ORA-01858 Error Message

Question

I have a question regarding date format error. So i have this query

SELECT TO_DATE (PS.PROJECT_START_DT, 'MM/DD/YYYY')

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

my table structure is this

PROJECT_START_DT PROJECT_END_DT JOB_NAME
2/5/2015         4/2/2015       W-IGG
2/18/2015        3/19/2015      W14056
2/5/2015         3/31/2015      W14013

Please help me with my issue with date format

Is project_start_dt a date? Or a varchar2? If it is a date, as the name implies, why are you calling to_date? Are you just trying to truncate the time component? — Justin Cave
my intention is just showing the date thru select statement which further i will use it for processing some data — Konz Mama
Do you want to select a date? Or do you want to select a varchar2 in a particular format? If you want to select a date, just select the date. — Justin Cave
If PROJECT_START_DT the applying to_date() is totally useless. As to_date() expects a VARCHAR parameter Oracle will first convert your date into a varchar (using a default format defined by your NLS settings), then the to_date() will convert that varchar back into a date (what it was to begin with). — a_horse_with_no_name

Lalit Kumar B Lalit Kumar B · Accepted Answer · 2015-02-06T05:55:44

SELECT TO_DATE (PS.PROJECT_START_DT, 'MM/DD/YYYY')

It makes no sense to use TO_DATE over a DATE column. You need TO_CHAR to display in the desired format using a proper FORMAT MODEL.

SQL> SELECT to_date(SYSDATE, 'mm/dd/yyyy') FROM dual;
SELECT to_date(SYSDATE, 'mm/dd/yyyy') FROM dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected


SQL>
SQL> SELECT to_char(SYSDATE, 'mm/dd/yyyy') FROM dual;

TO_CHAR(SY
----------
02/06/2015

SQL>

The DATE datatype has both date and time elements,

SQL> SELECT to_char(SYSDATE, 'mm/dd/yyyy hh24:mi:ss') dt_tm FROM dual;

DT_TM
-------------------
02/06/2015 11:28:32

SQL>

If you want to use the DATE value in any date operation, then you don't have to convert it into anything at all, simply use the DATE value directly for date operations.

Update On OP's request about the query here http://pastie.org/9891465

In that query, you don't have to use TO_CHAR in the sub-query. Use TO_CHAR only in the main select since that is what will display the values. The start_date and end_dt in the sub-query is an INPUT to the outer query and should remain as DATE values and need not convert it into a string.

SELECT LEVEL WEEK_NUM_INCR,
  TO_CHAR (start_date + (LEVEL - 1) * 7, 'WW') WEEK_POSITION
  /* WEEK POSITION FOR THE WHOLE YEAR */
  ,
  TO_CHAR (start_date + (LEVEL - 1) * 7, 'MM/DD/YYYY') START_WEEK_DATE,
  TO_CHAR (start_date + (LEVEL) * 7, 'MM/DD/YYYY') END_WEEK_DATE
FROM
  (SELECT PS.PROJECT_START_DT start_date,
    PS.PROJECT_END_DT end_date
  FROM PROJECT_SPAN PS
  WHERE PS.JOB_NAME = 'W-IGG'
  )
  CONNECT BY start_date + (LEVEL - 1) * 7 < end_date;

ORA-01858 Error Message

1 Answers