Re: problem with date in oracle 10g

From: Roberto Nenni <rnennix_at_xeim.it>
Date: Wed, 19 Dec 2007 12:11:56 +0100
Message-ID: <4768fc7d$0$16031$5fc30a8@news.tiscali.it>

"Vladimir M. Zakharychev" <vladimir.zakharychev_at_gmail.com> ha scritto nel messaggio
news:71288a6d-636f-4f58-9a1c-a1fea3a2a21d_at_t1g2000pra.googlegroups.com...
> 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?

this is now very difficult for me, because a few minutes ago i re-execute the query and now it work correctly
i am thinking about a problem in the database, because last month we had a similar problem with a function using dates (not this one) and we solve it changing the query without using it;
all theese functions work correctly in other installation of this application
the oracle version is 10.2.0.3.0 (like another ones in witch all works correctly)

> 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.

if there is a problem in testing null condition there is no effects in my problem because the date to be compared are never null

> 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.)
all date in our application are stored as datatype = date and if i query them with a select date from xxx or select to_char(date,'yyyy-mm-dd') from xxx I always see the correct year

thanks anyway
roberto Received on Wed Dec 19 2007 - 05:11:56 CST