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

Home -> Community -> Usenet -> c.d.o.misc -> Re: Using Greatest function on 2 dates, one can be null

Re: Using Greatest function on 2 dates, one can be null

From: Bricklen <bricklen-rem_at_yahoo.comz>
Date: Mon, 17 May 2004 18:17:10 GMT
Message-ID: <Gk7qc.15533$j6.8321@edtnps84>


Kelly Gallagher wrote:

> Hello,
> I have been searching this newsgroup and trying some code
> for a few hours but so far have been beating my head against
> a wall it seems.
>
> I need to get the greater value of two date fields, one of which
> might be null. I've tried using greatest(date1, nvl(date2,0)) and
> select greatest(date1, decode(date2,date2,null,0)) but neither are
> working since dates and numbers are not the same datatypes. I've
> been trying to find out how to convert a date to a simple number
> but cannot find that either. I think that Oracle's starting date
> is Jan 01, 1970; do I have to do something like this :
> GREATEST(date1, nvl(date2, to_date('01-JAN-1970')) ?
> That looks really ugly to me :)
> Any help would be appreciated. Thanks!
> Kelly Gallagher

How about something like this
eg.
select case when to_date(:date1,'DD/MM/YYYY') > to_date(:date2,'DD/MM/YYYY') or :date2 is null then :date1 else :date2 end from dual;

Also depends on Oracle version Received on Mon May 17 2004 - 13:17:10 CDT

Original text of this message

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