0

I have this sql query which retrieve records until 12/7/2016 the problem is, oracle returns records which is from 12/6/2016 and below. May I ask is this the correct behavior?

SELECT DISTINCT ACCOUNT_NO
FROM TBL_CLIENT_FINANCIAL_ACTIVITY cfa
WHERE CAST(tran_date as DATE) <= TO_DATE('12/07/2016');
jarlh
  • 42,561
  • 8
  • 45
  • 63
Eldon Hipolito
  • 704
  • 1
  • 8
  • 24
  • Do `SELECT ACCOUNT_NO, tran_date FROM ...`. Correct result? – jarlh Dec 07 '16 at 10:56
  • Yes. It does reflects correct result. The issue is the date comparison. – Eldon Hipolito Dec 07 '16 at 10:58
  • Move `CAST(tran_date as DATE)` and `TO_DATE('12/07/2016')` from WHERE to the select list. What happens? – jarlh Dec 07 '16 at 11:01
  • To clarify: Are you saying that the exact date 12/07/2016 is excluded, as if you had written `<` instead of `<=` ? – Henning Koehler Dec 07 '16 at 11:02
  • Outputs the same result. – Eldon Hipolito Dec 07 '16 at 11:02
  • @HenningKoehler, yes that's what I'm trying to say. If I instead use '12/08/2016' I could see results from '12/07/2016' – Eldon Hipolito Dec 07 '16 at 11:03
  • Hmm.. my only guess at this point is that it's related to [timezones](https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm) configured in the server and client, which may lead you to get dates of 12/07 in the result on the server side, but they display as 12/06 on the client side. You may be able to check this by returning the date of the account_no tuple both as string and as date type. – Henning Koehler Dec 07 '16 at 11:14

1 Answers1

1

I am assuming you are using MM/DD/YYYY? Declare it in your to_date().

to_date('12/07/2016','MM/DD/YYYY')

Also, let's trunc that new date to match the date you have stated, otherwise those leftover hours minutes seconds are later than your to_date value.

trunc(CAST(tran_date as DATE)) <= to_date('12/07/2016','MM/DD/YYYY')
JohnHC
  • 10,935
  • 1
  • 24
  • 40