Re: lesser of two dates

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Wed, 20 Jun 2007 17:35:39 -0700
Message-ID: <1182386139.641337.60020@w5g2000hsg.googlegroups.com>

On Jun 20, 5:41 pm, jobs <j..._at_webdos.com> wrote:
> Is there an oracle function that would return the lesser of two dates?
>
> Thanks for any help or information.

A quick setup:
CREATE TABLE T1(
  C1 DATE,
  C2 DATE); Create 12 rows in the table with random dates in the two date columns: INSERT INTO T1
SELECT
  TRUNC(SYSDATE)+TRUNC(DBMS_RANDOM.VALUE(1,100))-49,   TRUNC(SYSDATE)+TRUNC(DBMS_RANDOM.VALUE(1,100))-49 FROM
  DUAL
CONNECT BY
  LEVEL<=12;

Two methods, using DECODE with SIGN, and using LEAST: SELECT
  C1,
  C2,
  DECODE(SIGN(C1-C2),1,C2,C1) L1,
  LEAST(C1,C2) L2
FROM
  T1;

C1 C2 L1 L2

--------- --------- --------- ---------
25-JUL-07 17-MAY-07 17-MAY-07 17-MAY-07
02-JUN-07 21-MAY-07 21-MAY-07 21-MAY-07
31-JUL-07 28-JUL-07 28-JUL-07 28-JUL-07
17-JUN-07 10-MAY-07 10-MAY-07 10-MAY-07
05-JUL-07 03-AUG-07 05-JUL-07 05-JUL-07
28-JUL-07 23-MAY-07 23-MAY-07 23-MAY-07
01-AUG-07 17-JUN-07 17-JUN-07 17-JUN-07
28-JUN-07 28-MAY-07 28-MAY-07 28-MAY-07
20-MAY-07 11-JUN-07 20-MAY-07 20-MAY-07
21-JUN-07 09-JUN-07 09-JUN-07 09-JUN-07
26-JUL-07 24-JUL-07 24-JUL-07 24-JUL-07
22-JUL-07 06-JUN-07 06-JUN-07 06-JUN-07

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Wed Jun 20 2007 - 19:35:39 CDT