oracle sql查询以列出上个月的所有日期 [英] oracle sql query to list all the dates of previous month

问题描述

伙计们，我需要列出下个月的所有日期，如下所示

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
 )

强调文字

emphasized text

推荐答案

听起来您想要这样的东西

It sounds 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.

这篇关于oracle sql查询以列出上个月的所有日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！