CODEWITHSUNDEEP
sqloracle-database
JLSG
JLSG

Reputation: 27

ORACLE SQL SYSDATE and between

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.

Upvotes: 0

Views: 2377

Answers (2)

doberkofler
doberkofler

Reputation: 10361

There is some strange date conversions in your where clause:

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

  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 TRUNC(DATE_ENTERED) Between TRUNC(SYSDATE - 300) AND TRUNC(SYSDATE);

Upvotes: 1

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59476

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)

Upvotes: 2

Related Questions