• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 463
  • Last Modified:

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'
)
0
Michael
Asked:
Michael
  • 2
1 Solution
 
sdstuberCommented:
I assume you mean oracle sql,  not really pl/sql

WITH cte_datestable
     AS (SELECT     TO_DATE('20090601', 'yyyymmdd') + LEVEL - 1 AS datetime
               FROM DUAL
         CONNECT BY TO_DATE('20090601', 'yyyymmdd') + LEVEL - 1 <= TO_DATE('20090630', 'yyyymmdd'))
0
 
slightwv (䄆 Netminder) Commented:
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;
0
 
sdstuberCommented:
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
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now