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

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