Orcale sql语句 查询某一年没个月的第一天和最后一天,假设是2008年 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 select to_date('2008-'||rownum||'-01','yyyy-mm-dd'),add_months(to_date('2008-'||rownum||'-01','yyyy-mm-dd'),1)-1from '一个记录数超过12的表' TRY IT ..SQL> SELECT TO_DATE('2008'||LPAD(RN,2,'0')||'01','YYYY-MM-DD') "FIRST_DAY", 2 LAST_DAY(TO_DATE('2008'||LPAD(RN,2,'0')||'01','YYYY-MM-DD')) "LAST_DAY" 3 FROM DUAL, 4 ( 5 SELECT ROWNUM RN 6 FROM DUAL 7 CONNECT BY ROWNUM <= 12 8 )OB 9 ;FIRST_DAY LAST_DAY----------- -----------1/1/2008 1/31/20082/1/2008 2/29/20083/1/2008 3/31/20084/1/2008 4/30/20085/1/2008 5/31/20086/1/2008 6/30/20087/1/2008 7/31/20088/1/2008 8/31/20089/1/2008 9/30/200810/1/2008 10/31/200811/1/2008 11/30/200812/1/2008 12/31/200812 rows selectedSQL> SELECT trunc(sysdate,'mm'),last_day(sysdate) from dual;设置一个循环就可以了add_mouths 上月一号select add_months(trunc(sysdate,'mm'),-1) from dual 上月最后一天select trunc(sysdate,'mm')-1 from dual SELECT TRUNC(TO_DATE('2008' || LPAD(RN, 2, '0') || '01', 'YYYY-MM-DD'), 'mm') "FIRST_DAY", LAST_DAY(TO_DATE('2008' || LPAD(RN, 2, '0') || '01', 'YYYY-MM-DD')) "LAST_DAY" FROM DUAL, (SELECT ROWNUM RN FROM DUAL CONNECT BY ROWNUM <= 12) OB;查询结果: FIRST_DAY LAST_DAY1 2008-1-1 2008-1-312 2008-2-1 2008-2-293 2008-3-1 2008-3-314 2008-4-1 2008-4-305 2008-5-1 2008-5-316 2008-6-1 2008-6-307 2008-7-1 2008-7-318 2008-8-1 2008-8-319 2008-9-1 2008-9-3010 2008-10-1 2008-10-3111 2008-11-1 2008-11-3012 2008-12-1 2008-12-31 写一个查询语句? 触发器无法删除? ProC 关于Oracle method 4 方法的问题 求Oracle中给分区表建本地索引的例子 求SQL语句--两个时间范围是否有交集 oracle 问题,来帮下忙先谢谢了 各位大虾,请问怎么给ORACLE传递数组呢??? 无法删除用于强制唯一/主键的索引 请看这个错误 救救我,我的数据库down了,在线等待!!!! 如何删除数据库 求一条Sql语句!
from '一个记录数超过12的表'
SQL> SELECT TO_DATE('2008'||LPAD(RN,2,'0')||'01','YYYY-MM-DD') "FIRST_DAY",
2 LAST_DAY(TO_DATE('2008'||LPAD(RN,2,'0')||'01','YYYY-MM-DD')) "LAST_DAY"
3 FROM DUAL,
4 (
5 SELECT ROWNUM RN
6 FROM DUAL
7 CONNECT BY ROWNUM <= 12
8 )OB
9 ;FIRST_DAY LAST_DAY
----------- -----------
1/1/2008 1/31/2008
2/1/2008 2/29/2008
3/1/2008 3/31/2008
4/1/2008 4/30/2008
5/1/2008 5/31/2008
6/1/2008 6/30/2008
7/1/2008 7/31/2008
8/1/2008 8/31/2008
9/1/2008 9/30/2008
10/1/2008 10/31/2008
11/1/2008 11/30/2008
12/1/2008 12/31/200812 rows selectedSQL>
select add_months(trunc(sysdate,'mm'),-1) from dual
上月最后一天select trunc(sysdate,'mm')-1 from dual
'mm') "FIRST_DAY",
LAST_DAY(TO_DATE('2008' || LPAD(RN, 2, '0') || '01', 'YYYY-MM-DD')) "LAST_DAY"
FROM DUAL, (SELECT ROWNUM RN FROM DUAL CONNECT BY ROWNUM <= 12) OB;
查询结果:
FIRST_DAY LAST_DAY
1 2008-1-1 2008-1-31
2 2008-2-1 2008-2-29
3 2008-3-1 2008-3-31
4 2008-4-1 2008-4-30
5 2008-5-1 2008-5-31
6 2008-6-1 2008-6-30
7 2008-7-1 2008-7-31
8 2008-8-1 2008-8-31
9 2008-9-1 2008-9-30
10 2008-10-1 2008-10-31
11 2008-11-1 2008-11-30
12 2008-12-1 2008-12-31