Oracle sql to display all dates of the previous month

Question

Guys, I have a requirement to list all the dates of the previous month, as shown below

20101201 20101202 20101203 20101204 20101205 .. .. .. .. .. .. .. .. 20101231 

Kindly let me know if this is a better way to do than this request.

 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 request, which says "Missing correct 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 ) 

the selected text

Answer 1

It looks like you want something like this

 SQL> ed Wrote file afiedt.buf 1 select to_char( add_months(trunc(sysdate,'MM'),-1) + level - 1, 2 'YYYYMMDD' ) 3 from dual 4 connect by level <= 5 last_day(add_months(trunc(sysdate,'MM'),-1)) - 6 add_months(trunc(sysdate,'MM'),-1) + 7* 1 SQL> / TO_CHAR( -------- 20101201 20101202 20101203 20101204 20101205 20101206 20101207 20101208 20101209 20101210 20101211 20101212 20101213 20101214 20101215 20101216 20101217 20101218 20101219 20101220 20101221 20101222 20101223 20101224 20101225 20101226 20101227 20101228 20101229 20101230 20101231 31 rows selected. 

Answer 2

for the current month:

 SELECT TO_CHAR (TRUNC (SYSDATE, 'MM'), 'YYYYMMDD')+(LEVEL - 1) each_date FROM DUAL a CONNECT BY LEVEL < (TO_NUMBER (TO_CHAR (TRUNC (SYSDATE, 'MM') - 1, 'DD'))+1) 

Answer 3

A little add_months will definitely make it better, such as

 select to_char(x,'yyyymmdd') from ( select add_months(trunc(sysdate,'MONTH'),-1)+rownum-1 x from all_objects ) where x<trunc(sysdate,'MONTH'); 

Answer 4

This may be a little easier to understand:

 select TO_CHAR(d, 'YYYYMMDD') from ( select ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1) + (ROWNUM - 1) d from DUAL connect by level <= 31 ) where d < TRUNC(SYSDATE, 'MM') 

However, the "connect by level" method is the most understandable, and, as described here , a faster way to generate a sequence of numbers. I don’t think there is any way to significantly improve your request.

Answer 5

Regarding the correct parenthesis, you are trying to concatenate strings in the wrong way:

 select TO_CHAR(TRUNC(SYSDATE,'MM')-1,'YYYYMMDD')-(level-1) as 

must work:

 select TO_CHAR(TRUNC(SYSDATE,'MM')-1,'YYYYMMDD') || '-' || To_Char(level-1) as 

Obviously, you do not want concatenation to occur. Therefore, I think you really want to add a level to TRUNC() -part

Fix:

 select TO_CHAR(TRUNC(SYSDATE,'MM') - 1 + level - 1,'YYYYMMDD') as EACH_DATE from dual A connect by level < (TO_NUMBER(TO_CHAR(TRUNC(SYSDATE,'MM')-1,'DD'))+1)