Need sql query to return all dates between two date ranges
Hello all,
I need a sql query that will bring back all dates between two date ranges.
For example if I want to see all the days between may 1/08 and may 31/08 then it would return a recordset with the following..
May 1/08
May 2/08
May 3/08
May 4/08
May 5/08
and so on....
If you wanted to return months and the date range was May1/08 to dec 1/08 it would return
May 1/08
Jun 1/08
Jul 1/08
Aug 1/08
and so on
Any ideas?
Thanks in advance
Ian
I need a sql query that will bring back all dates between two date ranges.
For example if I want to see all the days between may 1/08 and may 31/08 then it would return a recordset with the following..
May 1/08
May 2/08
May 3/08
May 4/08
May 5/08
and so on....
If you wanted to return months and the date range was May1/08 to dec 1/08 it would return
May 1/08
Jun 1/08
Jul 1/08
Aug 1/08
and so on
Any ideas?
Thanks in advance
Ian
Try this two queries:
SELECT DAYS
FROM
(SELECT TRUNC(SYSDATE,'MM')-1+LEVE
FROM DUAL
CONNECT BY PRIOR DBMS_RANDOM.VALUE(0,0.1) < LEVEL
AND LEVEL <= 365)
WHERE DAYS <= LAST_DAY(SYSDATE)
SELECT MIN(TO_CHAR(DAYS,'Mon d/rr')) MONTHS
FROM
(SELECT TRUNC(SYSDATE,'MM')-1+LEVE
FROM DUAL
CONNECT BY PRIOR DBMS_RANDOM.VALUE(0,0.1) < LEVEL
AND LEVEL <= 365)
GROUP BY TO_CHAR(DAYS,'YYYYMM')
ORDER BY TO_CHAR(DAYS,'YYYYMM')
SELECT DAYS
FROM
(SELECT TRUNC(SYSDATE,'MM')-1+LEVE
FROM DUAL
CONNECT BY PRIOR DBMS_RANDOM.VALUE(0,0.1) < LEVEL
AND LEVEL <= 365)
WHERE DAYS <= LAST_DAY(SYSDATE)
SELECT MIN(TO_CHAR(DAYS,'Mon d/rr')) MONTHS
FROM
(SELECT TRUNC(SYSDATE,'MM')-1+LEVE
FROM DUAL
CONNECT BY PRIOR DBMS_RANDOM.VALUE(0,0.1) < LEVEL
AND LEVEL <= 365)
GROUP BY TO_CHAR(DAYS,'YYYYMM')
ORDER BY TO_CHAR(DAYS,'YYYYMM')
ASKER
paquicuba:
I tried this code but I only get one date back. Am I doing something wrong or do I need to change something in here to get each first of the month for a date range?
Ian
I tried this code but I only get one date back. Am I doing something wrong or do I need to change something in here to get each first of the month for a date range?
Ian
Try to use any table with more rows than those that you need.
e.g.
SELECT DAYS
FROM
(SELECT TRUNC(SYSDATE,'MM')-1+ROWN
FROM ALL_OBJECTS
WHERE ROWNUM <= 365)
WHERE DAYS <= LAST_DAY(SYSDATE)
/
SELECT MIN(TO_CHAR(DAYS,'Mon d/rr')) MONTHS
FROM
(SELECT TRUNC(SYSDATE,'MM')-1+ROWN
FROM ALL_OBJECTS WHERE ROWNUM <= 365)
GROUP BY TO_CHAR(DAYS,'YYYYMM')
ORDER BY TO_CHAR(DAYS,'YYYYMM')
/
e.g.
SELECT DAYS
FROM
(SELECT TRUNC(SYSDATE,'MM')-1+ROWN
FROM ALL_OBJECTS
WHERE ROWNUM <= 365)
WHERE DAYS <= LAST_DAY(SYSDATE)
/
SELECT MIN(TO_CHAR(DAYS,'Mon d/rr')) MONTHS
FROM
(SELECT TRUNC(SYSDATE,'MM')-1+ROWN
FROM ALL_OBJECTS WHERE ROWNUM <= 365)
GROUP BY TO_CHAR(DAYS,'YYYYMM')
ORDER BY TO_CHAR(DAYS,'YYYYMM')
/
my code works...I wonder why you have not tried that!!??
SELECT TO_DATE('01-05-2008','DD-M
( SELECT 1 nothing FROM DUAL CONNECT BY LEVEL <= 1000 )
where TO_DATE('01-05-2008','DD-M
SELECT TO_DATE('01-05-2008','DD-M
( SELECT 1 nothing FROM DUAL CONNECT BY LEVEL <= 1000 )
where TO_DATE('01-05-2008','DD-M
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
you are right anand 2000v your code works perfectly. Sorry for the long delay on replying.
SELECT TO_DATE('01-05-2008','DD-M
( SELECT 1 nothing FROM DUAL CONNECT BY LEVEL <= 1000 )
where TO_DATE('01-05-2008','DD-M