Re: lesser of two dates

On Jun 20, 8:41 pm, DA Morgan <damor..._at_psoug.org> wrote:
> Charles Hooper wrote:
> > 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.
>
> Or with far less complexity:
>
> SELECT LEAST(C1,C2) FROM T1;
>
> Demo at:http://www.psoug.org/reference/number_func.html#nlst
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

All of that to answer a question which hasn't yet been asked.

David Fitzjarrell Received on Thu Jun 21 2007 - 08:54:54 CDT