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

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Mon, 17 May 2004 18:08:56 -0700
Message-ID: <1084842539.178205@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?

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Mon May 17 2004 - 20:08:56 CDT