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).
Here is what the dates look like in the table:
skip_locate :22-AUG-13
transaction_date :30-AUG-13
transaction_date - 60 :01-JUL-13
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;
No comments:
Post a Comment