Michael
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'
)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
this -- is silly because I have to cast a date to a date
but even so, it returns the wrong results
I know of no patch to address these problems
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;
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;
I know of no patch to address these problems
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'
)
select mydate from cte_datestable;