Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: problem with date in oracle 10g

Re: problem with date in oracle 10g

From: <fitzjarrell_at_cox.net>
Date: Tue, 18 Dec 2007 12:10:26 -0800 (PST)
Message-ID: <42663ad9-4e3f-41ef-b35d-79e322d7c7e5@r60g2000hsc.googlegroups.com>


On Dec 18, 1: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

Why are you trying to recreate the wheel? The LEAST() function has been around since at least 8.1.6:

SQL> select least(sysdate, sysdate-10) from dual;

LEAST(SYS



08-DEC-07 SQL> select least(sysdate - 20, sysdate +4) from dual;

LEAST(SYS



28-NOV-07 SQL> This does exactly what you want, from what you've posted.

David Fitzjarrell Received on Tue Dec 18 2007 - 14:10:26 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US