Re: problem with date in oracle 10g

On Dec 18, 10:30 pm, "Roberto Nenni" <rnen..._at_xeim.it> wrote:
> Hi to everybody and sorry for my english
>
> i have a problem with a query in a comparison of date
> it seems that oracle fails in comparring two date in a function
> this is the function:
> CREATE OR REPLACE FUNCTION whr_DtMin (
> DATA1 DATE,
> DATA2 DATE )
> RETURN DATE
> DETERMINISTIC
> AS
> BEGIN
> IF (DATA1 IS NULL AND DATA2 IS NULL)
> THEN
> RETURN NULL;
> END IF;
> IF (DATA2 IS NULL OR DATA1 < DATA2)
> THEN
> RETURN DATA1;
> ELSE
> RETURN DATA2;
> END IF;
> END;
>
> it must return the minimum date between the two received as parameters
>
> sometimes the functions don't work and return the wrong date
>
> i use it in a query like this:
> select field,field
> ,whr_dtMax(a.dtiniz,c.dtinmeCo) dtiniz
> ,whr_dtMin(a.dtfine,c.dtfimeCo) dtfine
> ,field,field
> from tablea a
> join tablec c etc...
>
> can anybody help me?
>
> tia
> roberto nenni

Can you provide a reproducible test case (that is, two dates that Oracle fails to compare properly?) What is your Oracle version (all 5 digits) and platform?

Off the top of my head I see only one potential issue with your code: the second IF fails to handle the case when DATA1 is NULL and DATA2 is not. Is DATA2 less than a NULL? Or greater? Or equal? Or at least not equal? None of the above, so (DATA1 < DATA2) will always evaluate to FALSE if DATA1 is NULL (as well as any other comparison.) In this case, DATA2 will always be returned. Not sure if this is expected behavior.

Another issue might be with how the dates are stored and, if they are stored as strings, how they are converted to DATEs: for example, TO_DATE('01/01/70','dd/mm/YY') will result in Jan 1st, 2070 being returned, though one could have meant Jan 1st, 1970. This is because YY always operates in current century (there's RR mask for two-digit years that has more complex rule for century decision.)

If both my guesses are wrong, I'd like to see a reproducible example.

Hth,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Tue Dec 18 2007 - 14:18:35 CST