Dominic Fichera Dominic Fichera - 5 months ago 17
SQL Question

SQL SELECT date from table, and calculate how many days since that date

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

MT0 MT0
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;
Comments