oracle sql查询列出上个月的所有日期

发布于 2024-10-11 01:52:51 字数 891 浏览 6 评论 0原文

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

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

请告诉我是否有比此查询更好的方法。

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)

另请让我知道此查询的问题，它显示“缺少右括号”

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
 )

强调文本

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

痞味浪人 2024-10-18 01:52:51

听起来你想要这样的东西

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.

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.

回复收藏 0 原文

逆蝶 2024-10-18 01:52:51

当月：

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)

for 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)

回复收藏 0 原文

望笑 2024-10-18 01:52:51

一点 add_months 肯定会让它变得更好，例如

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

A bit of add_months would definitely make it better, as in e.g.

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

回复收藏 0 原文

情域 2024-10-18 01:52:51

这可能更容易理解一点：

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')

但是，“按级别连接”方法是最清晰的，并且如这里，更快地生成数字序列的方法。我认为没有办法显着改善您的查询。

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 clear, and as described here, faster way to generate sequence of numbers. I don't think there is no way to dramatically improve your query.

回复收藏 0 原文

窗影残 2024-10-18 01:52:51

就正确的括号而言，您正在尝试以错误的方式连接字符串：

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

应该有效：

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

显然您不希望发生连接。因此，我认为您实际上想将级别添加到 TRUNC() 部分

修复：

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)

As far as the right parenthesis is concerned, you are trying to concatenate strings the wrong way:

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

should work:

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

Obviously you don't want the concatenation to happen. Therefore, I think you actually want to add the level to the 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)

回复收藏 0 原文

~没有更多了~