Calculate number of days between 2 dates

[mario_galeano]

I need to know how many days there are difference between 2 dates, I tried to use the DAYS_BETWEEN function, however I get this error:

the code is this:

SELECT DISTINCT 
a."DocNum",
a."DocDate",
DAYS_BETWEEN(c."DocNum",a."DocNum")
FROM OINV a
INNER JOIN INV1 b ON a."DocEntry" = b."DocEntry" 
INNER JOIN ODLN c ON b."BaseEntry" = c."DocEntry"
INNER JOIN DLN1 d ON c."DocEntry" = d."DocEntry" 
INNER JOIN ORDR e ON d."BaseEntry" = e."DocEntry"
INNER JOIN OSLP f ON a."SlpCode" = f."SlpCode" 

[lbreddemann · Accepted solution]

The DAYS_BETWEEN() function takes two DATES and returns the number of days between those dates.

The code provided uses the document numbers as input to the function. That's why you get the error message.

Why don't you try to use the "DocDate" columns instead?

SELECT DISTINCT 
               a."DocNum",
               a."DocDate",
               DAYS_BETWEEN(c."DocDate", a."DocDate")
FROM OINV a
INNER JOIN INV1 b ON a."DocEntry" = b."DocEntry" 
INNER JOIN ODLN c ON b."BaseEntry" = c."DocEntry"
INNER JOIN DLN1 d ON c."DocEntry" = d."DocEntry" 
INNER JOIN ORDR e ON d."BaseEntry" = e."DocEntry"
INNER JOIN OSLP f ON a."SlpCode" = f."SlpCode" 

[mario_galeano · Accepted solution]

Thank you very much lbreddemann , I didn't realize it was my mistake