1. y,yy,yyy,yyyy表示年的最后一位,二位,三位和四位
SQL> select to_char(sysdate,'y') from dual;
TO_CHAR(SYSDATE,'Y')
---------------------------------------------------------------------------
0
SQL> select to_char(sysdate,'yy') from dual;
TO_CHAR(SYSDATE,'YY')
---------------------------------------------------------------------------
10
SQL> select to_char(sysdate,'yyy') from dual;
TO_CHAR(SYSDATE,'YYY')
---------------------------------------------------------------------------
010
SQL> select to_char(sysdate,'yyyy') from dual;
TO_CHAR(SYSDATE,'YYYY')
---------------------------------------------------------------------------
2010
SQL>
2. d:一周的第几天;dd:一个月的第几天;ddd:一年的第几天
SQL> select to_char(sysdate,'d') from dual;
TO_CHAR(SYSDATE,'D')
---------------------------------------------------------------------------
4
SQL> select to_char(sysdate,'dd') from dual;
TO_CHAR(SYSDATE,'DD')
---------------------------------------------------------------------------
09
SQL> select to_char(sysdate,'ddd') from dual;
TO_CHAR(SYSDATE,'DDD')
---------------------------------------------------------------------------
160
3. w:一个月的第几周;ww:一年的第几周
SQL> select to_char(sysdate,'ww') from dual;
TO_CHAR(SYSDATE,'WW')
---------------------------------------------------------------------------
23
SQL> select to_char(sysdate,'w') from dual;
TO_CHAR(SYSDATE,'W')
---------------------------------------------------------------------------
2
SQL>
4. mm:一年的月份
SQL> select to_char(sysdate,'mm') from dual;
TO_CHAR(SYSDATE,'MM')
---------------------------------------------------------------------------
06
5. RM: 月份的罗马表示
SQL> select to_char(sysdate,'rm') from dual;
TO_CHAR(SYSDATE,'RM')
---------------------------------------------------------------------------
vi
SQL>
6. month和mon:月的表示
SQL> select to_char(sysdate,'month') from dual;
TO_CHAR(SYSDATE,'MONTH')
---------------------------------------------------------------------------
6月
7. dy和day:表示星期几
SQL> select to_char(sysdate,'dy') from dual;
TO_CHAR(SYSDATE,'DY')
---------------------------------------------------------------------------
星期三
SQL> select to_char(sysdate,'day') from dual;
TO_CHAR(SYSDATE,'DAY')
---------------------------------------------------------------------------
星期三
8.时间表示
SQL> select to_char(sysdate,'hh') from dual;
TO_CHAR(SYSDATE,'HH')
---------------------------------------------------------------------------
05
SQL> select to_char(sysdate,'hh24') from dual;
TO_CHAR(SYSDATE,'HH24')
---------------------------------------------------------------------------
17
SQL> select to_char(sysdate,'mi') from dual;
TO_CHAR(SYSDATE,'MI')
---------------------------------------------------------------------------
50
SQL> select to_char(sysdate,'ss') from dual;
TO_CHAR(SYSDATE,'SS')
---------------------------------------------------------------------------
45
SQL>
9. q:季度的表示
SQL> select to_char(sysdate,'q') from dual;
TO_CHAR(SYSDATE,'Q')
---------------------------------------------------------------------------
2
oracle时间相关函数
当前季度的第一天
SQL> select trunc(sysdate,'q') from dual;
TRUNC(SYSDATE,'Q')
------------------
2010-4-1
当前周的第一天(周日是第一天)
SQL> select trunc(sysdate,'d') from dual;
TRUNC(SYSDATE,'D')
------------------
2010-6-6
当前月的当前日
SQL> select trunc(sysdate,'dd') from dual;
TRUNC(SYSDATE,'DD')
-------------------
2010-6-9
当前月的最后一天
SQL> select last_day(sysdate) from dual;
LAST_DAY(SYSDATE)
-----------------
2010-6-30 17:54:5
sysdate后推两个月
SQL> select add_months(sysdate,2) from dual;
ADD_MONTHS(SYSDATE,2)
---------------------
2010-8-9 17:58:44
next_day(sysdate,2) 日期sysdate之后的第一周中,第2(指定星期的第几天)是什么日期
SQL> select next_day(sysdate,2) from dual;
NEXT_DAY(SYSDATE,2)
-------------------
2010-6-14 17:59:37
Months_between(f,s):日期f和s间相差月数
SQL> select months_between(sysdate,sysdate+1) from dual;
MONTHS_BETWEEN(SYSDATE,SYSDATE
------------------------------
-0.032258064516129
SQL>
找出sysdate的指定字段
SQL> select extract(month from sysdate) from dual;
EXTRACT(MONTHFROMSYSDATE)
-------------------------
6
SQL> select extract(year from sysdate) from dual;
EXTRACT(YEARFROMSYSDATE)
------------------------
2010
SQL>
上月末天:
SQL> select to_char(add_months(last_day(sysdate),-1),'yyyy-MM-dd') LastDay from dual;
LASTDAY
---------------------------------------------------------------------------
2010-05-31
上月今天
SQL> select to_char(add_months(sysdate,-1),'yyyy-MM-dd') PreToday from dual;
PRETODAY
---------------------------------------------------------------------------
2010-05-09
上月第一天
SQL> select to_char(add_months(last_day(sysdate)+1,-2),'yyyy-MM-dd') firstDay from dual;
FIRSTDAY
---------------------------------------------------------------------------
2010-05-01
要找到某月中所有周五的具体日期
SQL> SELECT to_char(b.a,'YY-MM-DD')
2 FROM ( SELECT trunc(SYSDATE,'mm')+ROWNUM-1 a
3 FROM dba_objects where rownum < 32 ) b
4 WHERE to_char(b.a,'day')='星期五'
5 ;
TO_CHAR(B.A,'YY-MM-DD')
---------------------------------------------------------------------------
10-06-04
10-06-11
10-06-18
10-06-25
SQL>
--转自