CODEWITHSUNDEEP
sqloracle-databasedatebetween
Benedict Solpico
Benedict Solpico

Reputation: 137

SQL Query with between two dates showing even those not between

select ktl.id, kth.trans_dte, kth.trtype, kp.match_code,ktl.net_amount,ktl.gross_amount,ktl.db_portfolio_type,ktl.cr_portfolio_type from k$transaction_lines ktl 
    left join k$transaction_header kth on ktl.id=kth.id 
    left join k$portfolio kp on kp.id = (CASE WHEN ktl.db_portfolio_type = 'C' THEN ktl.db_portfolio ELSE ktl.cr_portfolio END) 
    where to_char(kth.trans_dte,'DD-MON-YY') >= '22-AUG-16' 
    and to_char(kth.trans_dte,'DD-MON-YY') <= '27-AUG-16'
    and ktl.db_portfolio_type <> 'I' 
    and ktl.cr_portfolio is not null 
    order by  kth.trans_dte, ktl.id, kp.match_code, kth.trtype

This is my query. I just want to know if I have something wrong with my where clause kth.trans_dte. I only want to get Transactions from August 22 to Aug 27 but it I am getting transactions before that date, february and march are included when they shouldn't be. I wonder why.. Is there a problem with my code or is it something with the db that I don't know. Thanks!

Upvotes: 0

Views: 50

Answers (5)

Benedict Solpico
Benedict Solpico

Reputation: 137

Sorry, I solved it just now. I can't use to_char when doing that condition that is why it gives the wrong results.

Upvotes: 0

Faraz Ahmed
Faraz Ahmed

Reputation: 1607

try using between Keyword and to_date function in Query like

where kth.trans_dte between to_date('22-AUG-16') 
and to_date('27-AUG-16')
and ktl.db_portfolio_type <> 'I' 
and ktl.cr_portfolio is not null 
order by  kth.trans_dte, ktl.id, kp.match_code, kth.trtype

Upvotes: 0

Zahiro Mor
Zahiro Mor

Reputation: 1718

you convert your field to char with to_char and then try to compare it datewise. to_char does exactly what it says - converts to a char string so you'll get dates according to ascii representation of the string

try using to_date instead - on both sides of the condition (s)

Upvotes: 1

Saeed ur Rehman
Saeed ur Rehman

Reputation: 737

select ktl.id, kth.trans_dte, kth.trtype, kp.match_code,ktl.net_amount,ktl.gross_amount,ktl.db_portfolio_type,ktl.cr_portfolio_type from k$transaction_lines ktl 
    left join k$transaction_header kth on ktl.id=kth.id 
    left join k$portfolio kp on kp.id = (CASE WHEN ktl.db_portfolio_type = 'C' THEN ktl.db_portfolio ELSE ktl.cr_portfolio END) 
    where (to_char(kth.trans_dte,'DD-MON-YY') >= '22-AUG-16' 
    and to_char(kth.trans_dte,'DD-MON-YY') <= '27-AUG-16')
    and ktl.db_portfolio_type <> 'I' 
    and ktl.cr_portfolio is not null 
    order by  kth.trans_dte, ktl.id, kp.match_code, kth.trtype

Try this.

Upvotes: 0

Rohit
Rohit

Reputation: 89

try using BETWEEN instead of 2 conditions .

Upvotes: 0

Related Questions