CODEWITHSUNDEEP
sqloracle-databasedate-arithmetic
user2405778
user2405778

Reputation: 497

compare date types using between

below is a query I am running to get all the accounts with a locate date not between the date of the transaction and transaction date - 60. When I run it, the query returns this rows that are incorrect. When looking into this problem I made sure all dates are of the same time (they are all defined as date, not timestamp).

Edit: I have also tried putting the dates in trunc() and to_date() to no avail.

Here are the values of the dates that I am receiving:

skip_locate           :22-AUG-13
transaction_date      :30-AUG-13
transaction_date - 60 :01-JUL-13

EDIT 2: For those wondering about the dates, and if they are really from 2013:

skip_locate           :2013-08-22 00:00:00
transaction_date      :2013-08-30 00:00:00
transaction_date - 60 :2013-07-01 00:00:00

Also as I was playing around, when I take away the NOT in the NOT BETWEEN I get no results. This is wrong due to the fact that skip_locate is in fact between the two dates.

Here is the query:

SELECT DISTINCT rl.complaint_date, 
                  rl.complaint_amt, 
                  rl.date_served1, 
                  rl.date_served2,
                  rl.judgement_date,         
                  rl.skip_locate,                       
                  lcc.bal_range_min, 
                  lcc.bal_range_max, 
                  lcc.cost_range_min, 
                  lcc.cost_range_max, 
                  lcc.court, 
                  ah.ACCOUNT, 
                  ah.transaction_code, 
                  ah.transaction_date,
                  ah.transaction_date - 60 "t - 60",
                  ah.rule_id, 
                  ah.amount, 
                  ah.description,                      
                  r.state, 
                  r.zip_code, 
                  z.county                      
  FROM racctrel r, 
       ziplist z, 
       legal_court_cost lcc, 
       racctlgl rl,
       legal_transaction_review ah
  WHERE substr(r.zip_code,1,5) = z.zip
  AND r.state = lcc.state
  AND REPLACE(lcc.county,' ','') = REPLACE(upper(z.county),' ','')
  AND r.ACCOUNT = rl.ACCOUNT
  AND r.ACCOUNT = ah.ACCOUNT
  AND lcc.transaction_code = ah.transaction_code
  AND lcc.transaction_code in (2,31)
  AND lcc.end_date IS NULL
  AND ah.batch_id = 257
  and rl.skip_locate not between ah.transaction_date and ah.transaction_date - 60;

Upvotes: 0

Views: 107

Answers (1)

David Aldridge
David Aldridge

Reputation: 52346

In a BETWEEN predicate you place the earliest value first and the latest one second, so the code should be:

... BETWEEN ah.transaction_date - 60 and ah.transaction_date

If you had two dates and were not sure which was earliest and which latest, you would:

... BETWEEN Least(date_1, date_2) and Greatest(date_1, date_2)

Upvotes: 1

Related Questions