Re: problem with date in oracle 10g

On Dec 18, 11:12 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Dec 18, 2: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
>
> It appears that you are trying to reproduce the functionality of a
> built-in function. Take a look at the following:
> SELECT
> LEAST('01-JAN-2007','01-FEB-2007') FIRST_DATE
> FROM
> DUAL;
>
> FIRST_DATE
> -----------
> 01-FEB-2007
>
> The above was returned because, alphabetically 01-FEB-2007 sorts
> before 01-JAN-2007. Now, specifying both as dates:
> SELECT
> LEAST(TO_DATE('01-JAN-2007','DD-MON-YYYY'),TO_DATE('01-FEB-2007','DD-
> MON-YYYY')) FIRST_DATE
> FROM
> DUAL;
>
> FIRST_DAT
> ---------
> 01-JAN-07
>
> Trying again with a null for one of the dates:
> SELECT
> LEAST(NULL,TO_DATE('01-FEB-2007','DD-MON-YYYY')) FIRST_DATE
> FROM
> DUAL;
>
> FIRST_DATE
> ==========
> (null)
>
> You will have to work on the last result, possibly by using a DECODE
> function. No PL/SQL is necessary.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.

This last example might answer why the wheel's being reinvented - LEAST() says that it doesn't know the answer when one or both arguments are NULL, while the OP tries to implement additional rules for the case when one of the arguments is NULL (for example, that unknown, NULL, date is always preceding known dates...) Indeed, such rule can be forced by wrapping LEAST into DECODE or CASE or maybe NVL, but this might have not been obvious to the OP.

Regards,

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