CODEWITHSUNDEEP
sqloracle-database
CEM30
CEM30

Reputation: 1

Change system date suggestions nedded

We have a new software package that allows the company to write SQL code to be place on Query portals. We have several reports we wish to code using the previous day as one of the selections. If a report is ran on MONDAY we want to automatically select the previous FRIDAY as the selection date, We have ORACLE SQL DEVELOPER 4.1. The code we are trying to use is listed below:

SELECT ALERT_CD,ALERT_KEY,CHG_DTM,
CASE  
WHEN TO_CHAR(SYSDATE,'fmday')='sunday' 
THEN SYSDATE-2 
WHEN TO_CHAR(SYSDATE,'fmday')='monday' 
THEN SYSDATE-3 
ELSE SYSDATE-1 
END "change" 
FROM SG00400T 
WHERE ALERT_CD='AUTO'
and CHG_DTM >= to_date('SYSDATE', 'mm/dd/yyyy')

The error we are receiving:

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.

The CHG_DTM is a date/time field which could be part of the problem we do not full understand at this time. Any help would be greatly appreciated.

Upvotes: 0

Views: 47

Answers (1)

user330315
user330315

Reputation:

The expression to_date('SYSDATE', 'mm/dd/yyyy') is are trying to convert the string constant 'SYSDATE' to a date - which can't work.

But you should never, ever call to_date() on a value that is already a date. That will first convert the date value to a varchar just to convert that varchar back to a date which it was to begin with.

So the to_date() function is wrong at that place to begin with. Most probably you want:

and CHG_DTM >= trunc(SYSDATE)

Upvotes: 6

Related Questions