-How do I create a table with all dates from 2000 until today in it?
It's rather simple in plain SQL, no PL/SQL or scripting required.
SQL> create table date1 (d date);
Table created.
SQL> insert
into date1(d)
select
to_date('00-01-01','YY-DD-MM') + level
from
dual
where
(to_date('00-01-01','YY-DD-MM')+level) < sysdate
connect
by level<=1000000
/
2530 rows created.
SQL> select min(d) s, max(d) e from date1;
S E
--------- ---------
02-JAN-00 05-DEC-06
SQL>
6 comments:
Dear Sir,
I have tried the following query but only one date produced...kindly guid me ...
SQL> select to_date('00-01-01','YY-DD-MM') + level from dual where (to_date('00-01-01','YY-DD-MM')+level) < sysdate connect by level<=1000000;
TO_DATE('
---------
02-JAN-00
SELECT TO_DATE('01-02-2003','DD-MM-YYYY')+ROWNUM FROM ( SELECT 1 nothing FROM DUAL CONNECT BY LEVEL <= 1000 )
Is it possible to form a query that will return all of these dates without creating a temporary table?
Hi,
It's very useful! =)
How could I generate these for monthes/year only?
Something like:
period: 01/2000 (MM/YYYY) to Sysdate
Expected return:
1 02/2000
2 03/2000
3 04/2000
4 05/2000
5 ...
6 06/2011
Thanks in advance
Interesting. I did implemented this query and didn't got the desired results but what actually happened was my system totally got hanged up. Then I realized what I did wrong was that instead of using < I put > in my query. I also wanted to know the same that Carlos has requested for.
sap upgrade testing
hi there I am having trouble wit this part:
SQL> insert
into date1(d)
select
to_date safemeds ('00-01-01','YY-DD-MM') + level
from
dual
where
(to_date('00-01-01','YY-DD-MM')+level) <
can you tell me what I am doing wrong please??
Post a Comment