Oracle SQL - DateDiff Issue

Question

I am getting a 'Missing Expression' error. Do you see the problem? It's probably something stupid.

SELECT CASE WHEN a.CODE = 'CONTACT_CLIENT' THEN (@DATEDIFF('DD',a.LASTMODIFIED,w.DATE_OPEN)) END AS "MET"
  FROM WORKORD w
  INNER JOIN WORKDET wd on wd.TTNUM = w.SEQ_ID
  INNER JOIN ACTIONS a on a.SEQ_ID = wd.ACTION

possible duplicate of [Calculate difference between 2 date / times in Oracle SQL](http://stackoverflow.com/questions/1096853/calculate-difference-between-2-date-times-in-oracle-sql) — Allan, Jan 06 '15 at 20:50

score 4 · Answer 1 · answered Jan 06 '15 at 20:52

datediff isn't a function in Oracle. You also wouldn't prefix a function name with the @ sign.

Assuming that a.lastModified and w.date_open are both of type date, you can simply subtract them to get a difference in days.

CASE WHEN a.CODE = 'CONTACT_CLIENT' 
     THEN a.lastModified - w.date_open
 END

If you want an integer number of days (since date values always include a time component, subtracting two dates will frequently result in a non-integer number of days), throw in a trunc or a round depending on what you want to do.

Of course, I'm not sure why you don't have an ELSE in your CASE statement or why you have identifiers with underscores between words (date_open) and identifiers without underscores (lastModified). But that doesn't appear to be part of your question.

score 3 · Answer 2 · answered Jan 06 '15 at 20:51

Are you refering the @DATEDIFF conversion function from Oracle GoldenGate ?

This will not work in the Oracle RDBMS. There, you will have to use the minus operator:

a.LASTMODIFIED - w.DATE_OPEN

The result will be in fractional days. If you only need the number of days, you will have to trucate the result. Giving:

SELECT CASE WHEN a.CODE = 'CONTACT_CLIENT' THEN
                 TRUNC(a.LASTMODIFIED - w.DATE_OPEN)
       END AS "MET"
...

See http://www.dba-oracle.com/t_date_arithmetic.htm for a simple introduction to date arithmetics using Oracle.

score 1 · Answer 3 · answered Jan 06 '15 at 20:50

1

The problem is that @DATEDIFF is not a function in Oracle. Try trunc(a.LASTMODIFIED - w.DATE_OPEN).

answered Jan 06 '15 at 20:50

Allan

17,141
4
52
69

Oracle SQL - DateDiff Issue

3 Answers3