Thank you Bricklen and Daniel; the select case statement is
what I needed - I was just trying to avoid having to hard code
a dummy date in my select. Thanks both of you for your help!!
Kelly
Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1084842539.178205_at_yasure>...
> Bricklen wrote:
> > 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
>
> Alternatively:
>
> SELECT GREATEST(NVL(date1, TO_DATE(01-JAN-1800')), NVL(date2,
> TO_DATE(01-JAN-1800')))
> INTO x
> FROM dual;
>
> Any chance they could both be NULL?
Received on Tue May 18 2004 - 08:00:21 CDT
