Link to home
Start Free TrialLog in
Avatar of Michael
MichaelFlag for United States of America

asked on

how to create date range temp table

Below is a t-sql example for creating date range temp table.  How is done in PL/SQL?

WITH CTE_DatesTable
AS
(
  SELECT CAST('20090601' as datetime) AS [date]
  UNION ALL
  SELECT DATEADD(dd, 1, [date])
  FROM CTE_DatesTable
  WHERE DATEADD(dd, 1, [date]) <= '20090630'
)
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

What is the logic here?

You can create a list using connect by but I'm not following the date logic:

with cte_datestable as (
select sysdate+level mydate from dual connect by level <= sysdate-to_date('20090630','YYYYMMDD')
)
select mydate from cte_datestable;
it's a recursive WITH clause which is only supported in 11gR2

however,  there is a problem with recursing on dates directly.

for example  - this will produce an ORA-1790 error
WITH cte_datestable (datetime)
     AS (SELECT TO_DATE('20090601', 'yyyymmdd') AS datetime FROM DUAL
         UNION ALL
         SELECT datetime + 1
           FROM cte_datestable
          WHERE datetime <= TO_DATE('20090630', 'yyyymmdd'))
SELECT *
  FROM cte_datestable;

Open in new window


this -- is silly because I have to cast a date to a date
but even so, it returns the wrong results

WITH cte_datestable (datetime)
     AS (SELECT cast(TO_DATE('20090601', 'yyyymmdd') as date) AS datetime FROM DUAL
         UNION ALL
         SELECT datetime + 1
           FROM cte_datestable
          WHERE datetime <= TO_DATE('20090630', 'yyyymmdd'))
SELECT *
  FROM cte_datestable;

Open in new window



I know of no patch to address these problems