I have a function (daysBetween)
CREATE OR REPLACE FUNCTION daysBetween
(
p_date1 DATE,
p_date2 DATE
)
RETURN NUMBER
AS
BEGIN
RETURN (TRUNC(p_date1) - TRUNC(p_date2));
END;
With this function i have query
SELECT COALESCE(daysBetween(to_date('11-07-2003','mm-dd-yyyy'),
to_date(datetrx,'mm-dd-yyyy')),0)
from payment;
The result is wrong.it is showing wrong difference.
In your previous question you said datetrx is a DATE, so you should not be using it as an argument to to_date(). You're doing an implicit conversion to a string, and then an explicit conversion back. From that question it looks like your NLS_DATE_FORMAT is dd/mm/yyyy. So you're really doing this for the second argument passed to the function:
to_date(to_char(datetrx, 'dd/mm/yyyy'),'mm-dd-yyyy')
As long as day part of the datetrx value is <= 12 this will appear to work but will give you a different date than you started with - as you're swapping the day and month values over. That may be why you're getting the wrong answer from the function. If the day was later than the 12th of the month then it would error though.
But you don't need to convert it at all, just do:
SELECT COALESCE(daysBetween(to_date('11-07-2003', 'mm-dd-yyyy'), datetrx), 0)
from payment;
Or using ANSI syntax as suggested before:
SELECT COALESCE(daysBetween(date '2003-11-07', datetrx), 0)
from payment;
It's also possible you wanted to compare against July 11th rather then November 7th, which would give you the wrong result even if your NLS_DATE_FORMAT is actually mm/dd/yyyy. Impossible to tell without any sample data and expected and actual results.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments