oracle mon和month,oracle函数（日期时间表示）

author:skate

time:2010-06-09

oracle函数(日期时间表示)

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>

------end-------