oracle函数（日期时间表示）_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 1977 | 回复: 0

主题： oracle函数（日期时间表示）

lxk

注册用户

等级：上尉
经验：547
发帖：109
精华：0
注册：2011-8-18
状态：离线
发送短消息息给lxk

加好友发送短消息息给lxk

发消息

发表于：

2015-5-21 10:45:53 | [全部帖] [楼主帖]

楼主

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>

--转自