0

I'm looking to calculate how many days have passed since a specific date, retrieved from a table in my database. Based on the info I've found on W3Schools (Here), I have attempted using DATEDIFF, but am coming up against a couple of different errors I can't seem to work around.

I have included my code below, and based on this, what I want to happen is this: Select the "DD" from the "Wave_Data" table, and, based on "sysdate", work out how many days have lapsed since then.

SELECT DATEDIFF(WEEKDAY,:P1_DD,SYSDATE)
FROM WAVE_DATA
WHERE WAVE_NUMBER = :P1_WAVE;

The final calculation would then be inputted into a text field within my ApEx database.

Thank you in advance for any help you may be able to provide,

Dominic

Sparky
  • 14,967
  • 2
  • 31
  • 45
Dominic Fichera
  • 3
  • 5
  • What errors are you getting? – artm Jun 18 '16 at 12:53
  • A couple of different ones, but the most recent/current error is: Failed to parse SQL query! ORA-00904: "DATEDIFF": invalid identifier – Dominic Fichera Jun 18 '16 at 12:54
  • If this is Oracle, which it looks like, you can subtract dates. Similar to [this](http://stackoverflow.com/questions/28406397/datediff-function-in-oracle) – Hashman Jun 18 '16 at 12:59
  • Tag with the database you are using. – Tim Biegeleisen Jun 18 '16 at 13:00
  • 1
    DATEDIFF is not a valid Oracle function – Sparky Jun 18 '16 at 13:09
  • @Hashman, I've followed the instructions from this link. New script looks like this: SELECT TO_DATE(:P1_DD, 'YYYY-MM-DD') - TO_DATE(SYSDATE, 'YYYY-MM-DD') FROM WAVE_DATA WHERE WAVE_NUMBER = :P1_WAVE; after this change, though, I receive this error: Oracle date format picture ends before converting entire input string. Based on [this](http://stackoverflow.com/questions/17206160/oracle-date-format-picture-ends-before-converting-entire-input-string) link, it might have something to do with the format of the dates. Any ideas? – Dominic Fichera Jun 18 '16 at 13:09
  • @TimBiegeleisen I'm running Oracle ApEx 4.1 – Dominic Fichera Jun 18 '16 at 13:11
  • @Sparky Do you know of any Oracle alternatives to this? Potentially what Hashman suggested? – Dominic Fichera Jun 18 '16 at 13:11
  • It would help if you posted sample data and expected results. – sstan Jun 18 '16 at 13:13

1 Answers1

1

In Oracle you can just subtract one Date from another to get the difference (in days) between them:

SELECT SYSDATE - :p1_dd
FROM   Wave_Data
WHERE  Wave_Number = :p1_wave;

If you want to know the difference between the dates without any time parts then you can do:

SELECT TRUNC( SYSDATE ) - TRUNC( :p1_dd )
FROM   Wave_Data
WHERE  Wave_Number = :p1_wave;

or

SELECT FLOOR( SYSDATE - :p1_dd )
FROM   Wave_Data
WHERE  Wave_Number = :p1_wave;
MT0
  • 143,790
  • 11
  • 59
  • 117
  • Should that be sysdate - :p1_dd? – artm Jun 18 '16 at 13:15
  • This works (almost) perfectly! I added a "TO_DATE" in there, and I'm good to go! Additionally, is there any way to say "Only do this if P1_DD is in the past"? Might be pushing the friendship, though! – Dominic Fichera Jun 18 '16 at 13:17
  • `CASE WHEN :p1_dd < SYSDATE THEN SYSDATE - :p1_dd END` will get the difference if it is in the past or `NULL` otherwise. – MT0 Jun 18 '16 at 13:20