CODEWITHSUNDEEP
sqloracle-databasewhere-clause
Hatik
Hatik

Reputation: 1159

Filtering by difference in dates in where SQL Oracle

I have a script that collects data from many different tables, and returns all the results, the script was not written by me and has 1000+ code lines. To simplify:

SELECT 
       date_of_opening,
       ...<many other columns>
FROM
       <some other sub queries>;

As it returns whole set of data, I thought to limit it, only to those that were opened in certain period of 30 days, I have tried:

SELECT *
FROM 
      (SELECT 
              date_of_opening,
              ...<many other columns>
       FROM
              <some other sub queries>)
WHERE date_of_opening - to_date('23.01.2011','dd.mm.yyyy') BETWEEN (-30) and (-1) ;

Which resulted in

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

However if I try the same query in select, it will show the difference as correct one:

SELECT 
       date_of_opening,
       date_of_opening - to_date('23.01.2011','dd.mm.yyyy') as difference,
       ...<many other columns>
FROM
       <some other sub queries>;

Assuming for example that date_of_opening is 31.12.2010 difference will be -23

I have also tried to first calculate difference and then filter it in where as where difference between (-30) and (-1) but got

ORA-00932: inconsistent datatypes: expected NUMBER got DATE

Datatype of past_of_opening is Date

What method can I use to filter the results, or what is the cause of such behavior?

Upvotes: 0

Views: 439

Answers (1)

Rene
Rene

Reputation: 10541

A period of 30 days might be:

WHERE date_of_opening  BETWEEN (to_date('23.01.2011','dd.mm.yyyy')-30) and (to_date('23.01.2011','dd.mm.yyyy'))

Assuming that date_of_opening is indeed a DATE.

Upvotes: 2

Related Questions