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
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Wed Jun 20 2007 - 20:41:18 CDT
