CODEWITHSUNDEEP
sqloracle-database
Isaac
Isaac

Reputation: 351

Need help querying Oracle date field

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.

Upvotes: 1

Views: 76

Answers (3)

sentha selvaratnam
sentha selvaratnam

Reputation: 51

The last line, looks as :

AND (HDATE.END_DATE IS NULL OR HDATE.END_DATE>=LAST_DAY(ADD_MONTHS(SYSDATE,-2)))

30-APR-14-00.00:00 >= 30-JUN-14-09.xx:xx (it includes the time part),

therefore it returns FALSE and you don't get the record.

To truncates the time part use TRUNC() function:

AND (HDATE.END_DATE IS NULL OR HDATE.END_DATE>=TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-2))))

Just a small hint that there are months with 31 days :)

Upvotes: 0

Joseph B
Joseph B

Reputation: 5679

As cdummy has answered, the TRUNC function needs to be used, such that only the date parts are compared and the time parts are ignored for comparison.

AND TRUNC(HDATE.START_DATE) <= TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-2)))
AND HNAME.PROGRAM_ID != 'XXX'
AND (HDATE.END_DATE IS NULL OR TRUNC(HDATE.END_DATE) >= TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-2))));

Also, you can check the time parts of START_DATE and END_DATE, by querying as below:

SELECT 
  TO_CHAR(HDATE.START_DATE, 'YYYY/MM/DD HH24:MI:SS'),
  TO_CHAR(HDATE.END_DATE,, 'YYYY/MM/DD HH24:MI:SS')
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 HNAME.PROGRAM_ID != 'XXX';

Upvotes: 0

cdummy
cdummy

Reputation: 455

Use Trunc() function to truncate the time associated with the date. Then only the dates can be compared as per your requirement.

Upvotes: 2

Related Questions