Re: problem with date in oracle 10g

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Tue, 18 Dec 2007 12:12:23 -0800 (PST)
Message-ID: <70479fac-c226-4d10-8895-7fe79f1d92ef@o42g2000hsc.googlegroups.com>

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. Received on Tue Dec 18 2007 - 14:12:23 CST