Re: Date Comparison in Oracle

[Kenny Melton <KMELTO1@TANDY.COM>]

Sandra,

Two ways come to mind - I don't know if either will be applicable in
your case.  The first involves using the Oracle "LEAST" function.  It
looks something like this:

BEGIN-SELECT
LEAST(DATE1, DATE2)

FROM fromstuff
WHERE wherestuff
END-SELECT

The above will return the earlier of the dates (you may compare more
than two dates).  There is also a "GREATEST" function that works the
exact opposite.

The second way that comes to mind would be to select the dates in julian
format:

TO_CHAR(DATEx,'yyyyddd')

move them to numeric fields and then do your comparisons.  But if you
are looking for the earliest date, you need to change your "let $Date3 =
Date2" statement to "let $Date3 = Date1" in your comparison below.

Hope (no pun intended) this helps,

Kenny Melton
Tandy Information Services
kmelto1@tandy.com

-----Original Message-----
From: Hope, Sandra [mailto:HopeS@DYNCORP.COM]
Sent: Thursday, September 24, 1998 9:58 AM
To: Multiple recipients of list SQR-USERS
Subject: Date Comparison in Oracle


I need to select the earlier of several dates within an SQR.
I've tried various date formats and using

  if $Date1 < Date2
        let $Date3 = Date2
  end-if

I don't get date errors but the program simply replaces each date
whether it
is earlier or not.  Is there a special format required for this syntax
or is
there a different way to compare dates?   Thanks All.

SHope