1、查询上月的所有日期:
SELECT TO_CHAR (TRUNC (TRUNC (SYSDATE, 'MM') - 1, 'month') + LEVEL - 1,
'yyyy-MM-dd')
AS month_day
FROM DUAL
CONNECT BY LEVEL <= TO_CHAR (LAST_DAY (TRUNC (SYSDATE, 'MM') - 1), 'dd');
2、查询当月的所有日期:
SELECT TO_CHAR (TRUNC (SYSDATE, 'month') + LEVEL - 1,
'yyyy-MM-dd')
AS month_day
FROM DUAL
CONNECT BY LEVEL <= TO_CHAR (LAST_DAY (TRUNC (SYSDATE, 'MM') - 1), 'dd');
3、查询上月月份:
SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYY-MM') FROM DUAL;
4、查询当月天数:
SELECT TO_CHAR (LAST_DAY (TRUNC (SYSDATE, 'mm')), 'DD') mounth_count
FROM DUAL;
5、查询当月第一天:
SELECT TRUNC (SYSDATE, 'month') FROM DUAL;
6、查询当月最后一天:
SELECT TO_CHAR (LAST_DAY (SYSDATE), 'YYYY-MM-DD') FROM DUAL;
7、计算两个日期之间天数:
SELECT TO_DATE ('2013-08-05', 'yyyy-MM-dd')
- TO_DATE ('2013-08-01', 'yyyy-MM-dd')
+ 1
AS count_day
FROM DUAL;
8、查询两个日期之间的所有日期:
SELECT ROWNUM, TO_DATE ('2013-01-02', 'yyyy-MM-dd') + ROWNUM - 1 AS allday
FROM DUAL
CONNECT BY ROWNUM <
TO_DATE ('2013-01-07', 'yyyy-MM-dd')
- TO_DATE ('2013-01-02', 'yyyy-MM-dd')
+ 2;