Re: Select dates between two dates

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 28 Dec 1998 21:38:31 GMT
Message-ID: <3687f8a6.14202902@192.86.155.100>

A copy of this was sent to doussot_at_gifrance.com (Yann Doussot) (if that email address didn't require changing) On 28 Dec 1998 20:41:49 GMT, you wrote:

> Hello,
>
> I would like to create a query (or procedure/function/package ...)
>that returns days between two dates. Eg:
>
>select ...(sysdate, sysdate + 2);
>
>1998-12-28
>1998-12-29
>1998-12-30
>
> Datas must be returned as a table because I need to perfom a
>join with other tables.
>
> (Oracle 8.0.5)
>
> Thanks for any hints.
>

Try this:

SQL> create or replace type myTableType as table of date;   2 /
Type created.

this query shows how to dynamically 'build a table' using static data. I select three rows from this virtual table since I called the constructor with three elements:

SQL>
SQL> select a.column_value val
  2 from the ( select myTableType(sysdate,sysdate+1,sysdate+2) from dual ) a   3 /

VAL

---

28-DEC-98
29-DEC-98
30-DEC-98

If you want to do a function to do this and just send the start/stop dates and have the function 'create' all of the interior rows, you can do this:

SQL>
SQL> create or replace function get_all_dates(p_start in date, p_stop in date)   2 return myTableType
  3 as
  4 l_rv myTableType := myTableType();   5 begin

  6      for i in 0 .. trunc(p_stop-p_start) loop
  7          l_rv.extend;
  8          l_rv(l_rv.count) := p_start+i;
  9      end loop;
 10      return l_rv;

 11 end;
 12 /

Function created.

and then query it like this, achieving the same effect (except the function builds the table on the fly)...

SQL> 
SQL> 
SQL> select a.column_value  val

  2 from THE ( select cast( get_all_dates( sysdate, sysdate+2 ) as mytableType )

  3                 from dual ) a

  4 /

VAL

---

28-DEC-98
29-DEC-98
30-DEC-98

SQL> Yes, you can join this:

SQL> select emp.ename, a.column_value val   2 from THE ( select cast( get_all_dates( sysdate, sysdate+2 )

  3                                                    as mytableType )
  4                 from dual ) a,
  5             emp

  6 /

ENAME VAL

---------- ---------
SMITH      28-DEC-98
SMITH      29-DEC-98
SMITH      30-DEC-98
ALLEN      28-DEC-98
ALLEN      29-DEC-98
ALLEN      30-DEC-98
WARD       28-DEC-98
WARD       29-DEC-98
WARD       30-DEC-98
JONES      28-DEC-98
JONES      29-DEC-98
JONES      30-DEC-98
MARTIN     28-DEC-98
MARTIN     29-DEC-98

...

use it in an 'in' statement:

SQL> select * from all_users where trunc(created) in   2 ( select trunc( a.column_value ) val

  3            from THE ( select cast( get_all_dates( sysdate-7, sysdate )
  4                                                    as mytableType )
  5                     from dual )  a )

  6 /

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
WEB$DDRIGGS                          5960 21-DEC-98
WEB$KLANDEFE                         5963 21-DEC-98

...

and so on....

>---
>Yann Doussot <doussot_at_gifrance.com>
>Grey Interactive - France - http://www.gifrance.com/
>Cell: +33 6 12 71 70 03 Work: + 33 1 46 84 85 00
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Dec 28 1998 - 15:38:31 CST