CODEWITHSUNDEEP
sqloracle-database
Neelu
Neelu

Reputation: 93

Comparing the days difference between 2 dates in oracle

In my where clause of the query I need to check if difference between 2 days is > 7 days. I tried the following and also tried datediff, dateadd, trunc, tochar but get different errors each time. Any help is apprecaited. For the follwoing I get ORA-01858: a non-numeric character was found where a numeric was expected:

date1-date2 > 7

Posting the query:

SELECT *
FROM frm_rg_ps_rg ps_rg
WHERE 

((SELECT COUNT(*)
  FROM frm_rg_ps_rg b
  WHERE ps_rg.patient_id = b.patient_id
  AND ps_rg.formdata_seq = b.formdata_seq
  AND ps_rg.psdate IS NOT NULL
  AND b.psdate IS NOT NULL
  AND ps_rg.psresult IS NOT NULL
  AND b.psresult IS NOT NULL) = 1)


OR NOT EXISTS
  (SELECT *
  FROM frm_rg_ps_rg c
  WHERE (ps_rg.psdate IS NOT NULL AND c.psdate IS NOT NULL AND ps_rg.psresult IS NOT NULL AND c.psresult IS NOT NULL)
  AND ps_rg.patient_id = c.patient_id
  AND ps_rg.formdata_seq = c.formdata_seq
  AND ps_rg.elemdata_seq!=c.elemdata_seq
  AND ps_rg.psresult != c.psresult    
  --AND to_char(ps_rg.psdate ,'yyyy-mm-dd') - to_char(c.psdate ,'yyyy-mm-dd') > 7
  --AND Trunc(ps_rg.psdate) > Trunc(c.psdate)+7
  AND ps_rg.psdate - c.psdate > 7  
  )

AND ps_rg.psresult IS NOT NULL
AND ps_rg.psdate IS NOT NULL;

create table frm_rg_ps_rg
(
    PATIENT_ID     NUMBER 
   ,STUDY_ID       NUMBER 
   ,PROCSTEP_ID    NUMBER 
   ,FORMDATA_SEQ   NUMBER 
   ,ELEMDATA_SEQ   NUMBER 
   ,PSUNITOS       VARCHAR2(3950 CHAR) 
   ,PSDATE         DATE 
   ,PSND           VARCHAR2(1 CHAR) 
   ,PSRESULT       NUMBER 
   ,PSUNITS        VARCHAR2(120) 
);

Upvotes: 1

Views: 331

Answers (3)

David דודו Markovitz
David דודו Markovitz

Reputation: 44941

Assuming your string columns hold date in format 'yyyy-mm-dd' (change it if needed)

to_date(date1,'yyyy-mm-dd') - to_date(date2,'yyyy-mm-dd') > 7

Demo for the OP

create table t (dt1 date,dt2 date);
insert into t (dt1,dt2) values (sysdate,sysdate-10); 

select * from t where (select dt1-dt2 from dual) > 7;

+---------------------+---------------------+
| DT1                 | DT2                 |
+---------------------+---------------------+
| 2016-12-14 10:59:34 | 2016-12-04 10:59:34 |
+---------------------+---------------------+

select * from t where dt1-dt2 > 7;

+---------------------+---------------------+
| DT1                 | DT2                 |
+---------------------+---------------------+
| 2016-12-14 10:59:34 | 2016-12-04 10:59:34 |
+---------------------+---------------------+

Upvotes: 1

Neelu
Neelu

Reputation: 93

This issue is resolved now: I tried this: AND (select (ps_rg.psdate - c.psdate) from dual)>7

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

I can think of a way to get this error with this statement:

date1 - date2 > 7

And with both being dates. This could occur if you have hidden characters in the row -- that is, invisible characters between the > and the 7 or right after the 7.

You might try removing that line and retyping it to see if the error goes away.

Upvotes: 1

Related Questions