Is there an oracle function that would return the lesser of two dates?

Thanks for any help or information.

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

Yes.


David Fitzjarrell

On Jun 20, 5:41 pm, jobs <j...@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.

Charles Hooper wrote:
> On Jun 20, 5:41 pm, jobs <j...@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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On Jun 20, 9:41 pm, DA Morgan <damor...@psoug.org> wrote:
> Charles Hooper wrote:
> > 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...@x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

Yes, of the two methods that I showed, LEAST is least complex.

But, what if you want to use the obvious method, the MIN function?
The DECODE(SIGN()) method can be messy when there are more than two
values involved, so what about MIN? (Note: column values have changed
since first run as the test table was dropped after the previous test
run):
WITH T AS
(SELECT
ROWNUM RN,
C1,
C2
FROM
T1)
SELECT
T.C1,
T.C2,
MIN(DV) L3
FROM
(SELECT
RN,
C1 DV
FROM
T
UNION ALL
SELECT
RN,
C2 DV
FROM
T) D,
T
WHERE
T.RN=D.RN
GROUP BY
T.RN,
T.C1,
T.C2;

C1 C2 L3
--------- --------- ---------
01-JUN-07 19-JUL-07 01-JUN-07
06-AUG-07 09-MAY-07 09-MAY-07
22-MAY-07 29-JUL-07 22-MAY-07
05-MAY-07 03-JUN-07 05-MAY-07
02-JUN-07 09-AUG-07 02-JUN-07
15-JUL-07 03-JUN-07 03-JUN-07
23-MAY-07 31-JUL-07 23-MAY-07
07-AUG-07 12-MAY-07 12-MAY-07
24-JUL-07 27-JUN-07 27-JUN-07
31-MAY-07 16-JUL-07 31-MAY-07
21-MAY-07 17-MAY-07 17-MAY-07
24-JUN-07 08-AUG-07 24-JUN-07

The above basically uses a UNION ALL between all of the C1 values and
the C2 values, uses MIN to determine the lowest value per original
ROWNUM and then ties the result back into the original data set. The
plan:
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 1 | 3 | 12 |
00:00:00.01 | 21 | | | |
|* 2 | HASH JOIN | | 1 | 3 | 24 |
00:00:00.01 | 21 | 1011K| 1011K| 1064K (0)|
| 3 | VIEW | | 1 | 12 | 12 |
00:00:00.01 | 7 | | | |
| 4 | COUNT | | 1 | | 12 |
00:00:00.01 | 7 | | | |
| 5 | TABLE ACCESS FULL | T1 | 1 | 12 | 12 |
00:00:00.01 | 7 | | | |
| 6 | VIEW | | 1 | 24 | 24 |
00:00:00.01 | 14 | | | |
| 7 | UNION-ALL | | 1 | | 24 |
00:00:00.01 | 14 | | | |
| 8 | VIEW | | 1 | 12 | 12 |
00:00:00.01 | 7 | | | |
| 9 | COUNT | | 1 | | 12 |
00:00:00.01 | 7 | | | |
| 10 | TABLE ACCESS FULL| T1 | 1 | 12 | 12 |
00:00:00.01 | 7 | | | |
| 11 | VIEW | | 1 | 12 | 12 |
00:00:00.01 | 7 | | | |
| 12 | COUNT | | 1 | | 12 |
00:00:00.01 | 7 | | | |
| 13 | TABLE ACCESS FULL| T1 | 1 | 12 | 12 |
00:00:00.01 | 7 | | | |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."RN"="D"."RN")
----------------------------------------------------------------------------

The plan using just DECODE or LEAST:
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-
Time | Buffers |
------------------------------------------------------------------------------------
| 1 | TABLE ACCESS FULL| T1 | 1 | 12 | 12 |
00:00:00.01 | 7 |
------------------------------------------------------------------------------------

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

On Jun 20, 8:41 pm, DA Morgan <damor...@psoug.org> wrote:
> Charles Hooper wrote:
> > On Jun 20, 5:41 pm, jobs <j...@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...@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

fitzjarrell@cox.net wrote:
> On Jun 20, 8:41 pm, DA Morgan <damor...@psoug.org> wrote:
>> Charles Hooper wrote:
>>> On Jun 20, 5:41 pm, jobs <j...@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...@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

No we can tell people it has already been answered here if they'd
searched the archives on many more topics.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org