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