CODEWITHSUNDEEP
oracle-databaseoracleformsoraclereportsora-00932
lakshminarayan
lakshminarayan

Reputation: 11

add dates to a bind variable date field

We are using oracle 12c forms and reports. In one of the query the users will enter the date through forms and the below mentioned query will fetch the required data.

SELECT COUNT(*)
 FROM T_APPLICATION_HDR,T_APPLN_PENSIONER
 WHERE APPLN_PK=APEN_APPLN_PK
 AND APPLN_DATE <=:APPLN_DATE
 AND APPLN_SECN_ID ='PV1'
 AND APPLN_STAT='04'

What i require is from the above query the data fetched will be prior to the date entered by the users. I want to add 10 days to the date entered by users and records populated based on that date.

i modified the above query like this

SELECT COUNT(*)
 FROM T_APPLICATION_HDR,T_APPLN_PENSIONER
 WHERE APPLN_PK=APEN_APPLN_PK
 AND APPLN_DATE <=:APPLN_DATE+10
 AND APPLN_SECN_ID ='PV1'
 AND APPLN_STAT='04'

It gives this error

ORA-00932: inconsistent datatypes: expected DATE got NUMBER.

How do i modify this query

i modified the above query like this

SELECT COUNT(*)
 FROM T_APPLICATION_HDR,T_APPLN_PENSIONER
 WHERE APPLN_PK=APEN_APPLN_PK
 AND APPLN_DATE <=:APPLN_DATE+10
 AND APPLN_SECN_ID ='PV1'
 AND APPLN_STAT='04'

For example if the appln_date entered is 10-may-2019, then the query should fetch records prior to 20-may-2019.

It gives this error

ORA-00932: inconsistent datatypes: expected DATE got NUMBER.

How do i modify this query

Upvotes: 1

Views: 465

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50027

'10-may-2019' is not a date, it's text string. In the first case Oracle is smart enough to convert the text string to a date. In the first case Oracle is able to do the conversion but in the second case it gets confused.

I suggest changing your code to be

SELECT COUNT(*)
 FROM T_APPLICATION_HDR,T_APPLN_PENSIONER
 WHERE APPLN_PK=APEN_APPLN_PK
 AND APPLN_DATE <= TO_DATE(:APPLN_DATE, 'DD-MON-YYYY') + INTERVAL '10' DAY
 AND APPLN_SECN_ID ='PV1'
 AND APPLN_STAT='04'

Upvotes: 1

Related Questions