I am trying to get a date using between SYSDATE and SYSDATE - 300.

SELECT date_entered
  FROM customer_order_join
 WHERE TO_CHAR(date_entered, 'YYYYMMDD') BETWEEN
       TO_DATE(sysdate, 'YYYYMMDD') AND TO_DATE(sysdate, 'YYYYMMDD') - 300

I am getting the following error :

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

CodePudding user response：

SYSDATE returns a DATE value. Never run TO_DATE() on a value which is already a DATE. Apart from that, BETWEEN ... AND requires the lower date first.

You can compare the DATE values directly, I guess you are looking for this:

SELECT DATE_ENTERED 
FROM CUSTOMER_ORDER_JOIN
WHERE DATE_ENTERED Between SYSDATE-300 AND SYSDATE

Most likely you like to compare just the date values, without time value. Then you could use this:

SELECT DATE_ENTERED 
FROM CUSTOMER_ORDER_JOIN
WHERE TRUNC(DATE_ENTERED) Between TRUNC(SYSDATE-300) AND TRUNC(SYSDATE) -- or TRUNC(SYSDATE) 1 to cover full day

Note, the query will not utilize an index on DATE_ENTERED, unless you have a function-based index on TRUNC(DATE_ENTERED). So you may prefer

SELECT DATE_ENTERED 
FROM CUSTOMER_ORDER_JOIN
WHERE DATE_ENTERED >= TRUNC(SYSDATE-300) 
   AND DATE_ENTERED < TRUNC(SYSDATE)

CodePudding user response：

There is some strange date conversations in your where clause:

1. To_Char(DATE_ENTERED, 'YYYYMMDD') If the column is already a date type just use the column without any conversation and if it is a string, use TO_CHAR.

2. TO_DATE(SYSDATE,'YYYYMMDD') No conversion needed

3. TO_DATE(SYSDATE,'YYYYMMDD')-300 No conversion needed

So maybe just:

SELECT DATE_ENTERED FROM CUSTOMER_ORDER_JOIN
WHERE DATE_ENTERED Between SYSDATE AND SYSDATE - 300