Guys i have a requirement to list all the dates of the previous month like below
20101201
20101202
20101203
20101204
20101205
..
..
..
..
..
..
..
..
20101231
kindly let me know if any better way to do than this query.
select TO_CHAR(TRUNC(SYSDATE,'MM')-1,'YYYYMMDD')-(level-1) as
EACH_DATE from dual A connect by level
< (TO_NUMBER(TO_CHAR(TRUNC(SYSDATE,'MM')-1,'DD'))+1)
Also please let me know the problem with this query it says "missing right parenthesis"
SELECT /*+ PARALLEL (A,8) */ /*+ DRIVING_STATE */
TO_CHAR(TRUNC(TRUNC(SYSDATE,'MM')-1,'MM'),'MONYYYY') "MONTH", TYPE AS "TRAFF", COLUMN, A_COUN AS "A_COUNT",COST FROM DATA_P B WHERE EXISTS
(
select TO_NUMBER(TO_CHAR(TRUNC(SYSDATE,'MM')-1,'YYYYMMDD')-(level-1)) EACH_DATE
from dual A connect by level < TO_NUMBER(TO_CHAR(TRUNC(SYSDATE,'MM')-1,'DD')+1)
WHERE A.EACH_DATE = B.DATE order by EACH_DATE ASC
)