OraFAQ Forum: SQL & PL/SQL » >= date field in Oracle not retrieving expected results (merged 2 by MC)

Home » SQL & PL/SQL » SQL & PL/SQL » >= date field in Oracle not retrieving expected results (merged 2 by MC) (Oracle Database 10g Release 10.2.0.3.0 - Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

>= date field in Oracle not retrieving expected results (merged 2 by MC) [message #617483]

Mon, 30 June 2014 10:49

ipisors
Messages: 24
Registered: May 2014
Location: USA [Arizona ... Desert]

Junior Member

Confused on why this happens. I have a query that is excluding records that I want to include.

The record in question has these values:

MEMBER_ID            NAME_FIRST           NAME_LAST                      START_DATE END_DATE  PROGRAM_ID INDICATOR               
-------------------- -------------------- ------------------------------ ---------- --------- ---------- -------------------------
M########            BOB                  JOHN                           01-FEB-10  30-APR-14 M90        plan

(changed values slightly to preserve HIPPA compliance).

So the end date is clearly 30-APR-14.

And yet when I run this SQL, the record does NOT come back:

SELECT 
  HNO.MEMBER_ID,
  HNAME.NAME_FIRST,
  HNAME.NAME_LAST,
  HDATE.START_DATE,
  HDATE.END_DATE,
  HNAME.PROGRAM_ID,
  HDATE.INDICATOR
FROM HCFA_NAME_ORG HNO
INNER JOIN NAME HNAME
ON HNO.NAME_ID = HNAME.NAME_ID
INNER JOIN HCFA_DATE HDATE
ON HNO.NAME_ID = HDATE.NAME_ID
WHERE INSTR(HNO.MEMBER_ID,'M',1,1)>0 AND
MEMBER_ID='M20012289' and INDICATOR='plan' AND
HDATE.START_DATE <=  LAST_DAY(ADD_MONTHS(SYSDATE,-2)) AND  
  HNAME.PROGRAM_ID != 'XXX'
  AND (HDATE.END_DATE IS NULL OR HDATE.END_DATE>=LAST_DAY(ADD_MONTHS(SYSDATE,-2)))

When I comment out the last line, it does come back:

Why is this? The value is clearly =LAST_DAY(ADD_MONTHS(SYSDATE,-2)) So why does this get excluded? Maybe I am not understanding something about Oracle date fields or how to properly query them.

Report message to a moderator

Re: >= date field in Oracle not retrieving expected results [message #617489 is a reply to message #617483]

Mon, 30 June 2014 11:05

ipisors
Messages: 24
Registered: May 2014
Location: USA [Arizona ... Desert]

Junior Member

Also just to clarify, the query I tried is posted in my first post - when I comment out the last line, the record comes back...when I leave the last line in, it does not come back. Even though the END_DATE field appears to be equal to or greater than the specified value

Report message to a moderator

Re: >= date field in Oracle not retrieving expected results [message #617491 is a reply to message #617483]

Mon, 30 June 2014 11:21

flyboy
Messages: 1903
Registered: November 2006

Senior Member

ipisors wrote on Mon, 30 June 2014 17:49

So the end date is clearly 30-APR-14.
...
Why is this? The value is clearly =LAST_DAY(ADD_MONTHS(SYSDATE,-2)) So why does this get excluded? Maybe I am not understanding something about Oracle date fields or how to properly query them.

I agree with the last sentence. In Oracle, DATE data type contains time component unless you get rid of it (e.g. using TRUNC function with proper format).

SQL> alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select LAST_DAY(ADD_MONTHS(SYSDATE,-2)) from dual;

LAST_DAY(ADD_MONTHS
-------------------
30.04.2014 18:15:00

So, "30.04.2014 midnight (or whichever time is stored there)" is not the same as "30.04.2014 18:15:00 (in my timezone)".

[Updated on: Mon, 30 June 2014 11:21]

Report message to a moderator

Re: >= date field in Oracle not retrieving expected results [message #617492 is a reply to message #617491]

Mon, 30 June 2014 11:42

ipisors
Messages: 24
Registered: May 2014
Location: USA [Arizona ... Desert]

Junior Member

Thank you!
Yes - I knew the last sentence was true as I'm a beginner. Thank you again.

Report message to a moderator

Previous Topic:	SELECT grant on view to a role.
Next Topic:	Display output like.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 22:41:08 CDT 2024