Date format and getting results form last 24 hours

Question

I am new to SQL and I am having an issue with the query below. I need to return the order sessions that were entered in the last 24 hours, the query returns all order sessions if I don't have the last statement in there, but I only need the ones from the last 24 hours. CCDBA.O_PAT.ORDER_DDT is not an Oracle Date a number that needs to be converted to a readable date using ddt.tochar.

SELECT DISTINCT
         CCDBA.O_PAT.SESSION_ID, 
         CCDBA.PATIENT.MEDREC_ID "MRN",
         CCDBA.PATIENT.VISIT_NUMBER "Account Number",
         CCDBA.PATIENT.LAST_NAME || ', ' || CCDBA.PATIENT.FIRST_NAME "Patient",
         CCDBA.PATIENT.DEPT_ID "Floor",
         CCDBA.PATIENT.ROOM_ID "Room",
         ddt.tochar(CCDBA.O_PAT.ORDER_DDT) "Order Date"
  FROM CCDBA.PATIENT
 INNER JOIN CCDBA.O_PAT ON CCDBA.O_PAT.PAT_SEQ = CCDBA.PATIENT.PAT_SEQ
 WHERE CCDBA.O_PAT.ORDER_ID = '681278'
  AND TO_DATE(ddt.tochar(CCDBA.O_PAT.ORDER_DDT), 'DD-MON-YY HH24:MI:SS')
        >= SYSDATE -1;

I get the folloing error:

ORA-01843: not a valid month
01843. 00000 -  "not a valid month"
*Cause:    
*Action:

The raw data from CCDBA.O_PAT.ORDER_DDT looks like this: 7686745377 The data from looks CCDBA.O_PAT.ORDER_DDT like this after converting using ddt.tochar: 02/20/14 09:58

Answer 1

You don't need to convert a date to a string and back again to compare dates. Simplify the condition:

WHERE CCDBA.O_PAT.ORDER_ID = '681278' AND
      CCDBA.O_PAT.ORDER_DDT >= SYSDATE - 1;

Also, if you are learning SQL, learn to use table aliases. The resulting queries are easier to write and to read:

SELECT DISTINCT o.SESSION_ID, 
       p.MEDREC_ID  as "MRN",
       p.VISIT_NUMBER as "Account Number",
       p.LAST_NAME || ', ' || p.FIRST_NAME as "Patient",
       p.DEPT_ID as "Floor",
       p.ROOM_ID as "Room",
       ddt.tochar(o.ORDER_DDT) as "Order Date"
FROM CCDBA.PATIENT p INNER JOIN
     CCDBA.O_PAT o
     ON o.PAT_SEQ = p.PAT_SEQ
WHERE o.ORDER_ID = '681278' AND c.ORDER_DDT >= SYSDATE - 1;

And, if you don't need DISTINCT, don't use it. It just wastes processing time when not needed.

EDIT:

For the revised date format:

WHERE CCDBA.O_PAT.ORDER_ID = '681278' AND
      TO_DATE(ddt.tochar(CCDBA.O_PAT.ORDER_DDT),
              'MM/DD/YYYY HH24:MI') >= sysdate - 1;