Dominic Fichera - 1 year ago 78

SQL Question

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

Answer

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;
```

Source (Stackoverflow)