TRUNC函数用法

TRUNC函数处理时间[@more@]

1.设置一下时间的显示样式
sec@secooler> alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

Session altered.

2.以系统时间为例，查看当前时间
sys@secooler> select sysdate from dual;

SYSDATE
-------------------
2010-03-31 00:22:48

3.不使用参数表示截取到日
sys@secooler> select trunc(sysdate) trunc_default from dual;

TRUNC_DEFAULT
-------------------
2010-03-31 00:00:00

4.截取到年
以下八种方法都可以完成截取到年的功能。
sys@secooler> select trunc(sysdate,'YEAR') trunc_year from dual;

TRUNC_YEAR
-------------------
2010-01-01 00:00:00

sys@secooler> select trunc(sysdate,'SYYYY') trunc_year from dual;

TRUNC_YEAR
-------------------
2010-01-01 00:00:00

sys@secooler> select trunc(sysdate,'YYYY') trunc_year from dual;

TRUNC_YEAR
-------------------
2010-01-01 00:00:00

sys@secooler> select trunc(sysdate,'SYEAR') trunc_year from dual;

TRUNC_YEAR
-------------------
2010-01-01 00:00:00

sys@secooler> select trunc(sysdate,'YYY') trunc_year from dual;

TRUNC_YEAR
-------------------
2010-01-01 00:00:00

sys@secooler> select trunc(sysdate,'YYY') trunc_year from dual;

TRUNC_YEAR
-------------------
2010-01-01 00:00:00

sys@secooler> select trunc(sysdate,'YY') trunc_year from dual;

TRUNC_YEAR
-------------------
2010-01-01 00:00:00

sys@secooler> select trunc(sysdate,'Y') trunc_year from dual;

TRUNC_YEAR
-------------------
2010-01-01 00:00:00

5.截取到月
以下四种方法都可以完成截取到月的功能。
sys@secooler> select trunc(sysdate,'MONTH') trunc_mounth from dual;

TRUNC_MOUNTH
-------------------
2010-03-01 00:00:00

sys@secooler> select trunc(sysdate,'MON') trunc_mounth from dual;

TRUNC_MOUNTH
-------------------
2010-03-01 00:00:00

sys@secooler> select trunc(sysdate,'MM') trunc_mounth from dual;

TRUNC_MOUNTH
-------------------
2010-03-01 00:00:00

sys@secooler> select trunc(sysdate,'RM') trunc_mounth from dual;

TRUNC_MOUNTH
-------------------
2010-03-01 00:00:00

6.截取到日
以下三种方法都可以完成截取到日的功能。
sys@secooler> select trunc(sysdate,'DDD') trunc_day from dual;

TRUNC_DAY
-------------------
2010-03-31 00:00:00

sys@secooler> select trunc(sysdate,'DD') trunc_day from dual;

TRUNC_DAY
-------------------
2010-03-31 00:00:00

sys@secooler> select trunc(sysdate,'J') trunc_day from dual;

TRUNC_DAY
-------------------
2010-03-31 00:00:00

7.截取到小时
以下三种方法都可以完成截取到小时的功能。
sys@secooler> select trunc(sysdate,'HH') trunc_hour from dual;

TRUNC_HOUR
-------------------
2010-03-31 00:00:00

sys@secooler> select trunc(sysdate,'HH12') trunc_hour from dual;

TRUNC_HOUR
-------------------
2010-03-31 00:00:00

sys@secooler> select trunc(sysdate,'HH24') trunc_hour from dual;

TRUNC_HOUR
-------------------
2010-03-31 00:00:00

8.截取到分钟
sys@secooler> select trunc(sysdate,'MI') trunc_minute from dual;

TRUNC_MINUTE
-------------------
2010-03-31 00:22:00

9.trunc应用--解决因时分秒不同导致无返回记录的问题
sys@secooler> create table t (x date,y timestamp);

Table created.

sys@secooler> insert into t values (sysdate,sysdate);

1 row created.

sys@secooler> update t set y=sysdate;

1 row updated.

sys@secooler> commit;

Commit complete.

sys@secooler> select * from t;

X Y
------------------- ------------------------------------
2010-03-31 00:31:20 31-MAR-10 12.31.28.000000 AM

此时我们如果直接查询x与y相等的记录是没有返回结果的。
sys@secooler> select * from t where x = y;

no rows selected

如果我们同时使用trunc截取到天，结果将不同
sys@secooler> select * from t where trunc(x,'DD')=trunc(y,'DD');

X Y
------------------- ------------------------------------
2010-03-31 00:31:20 31-MAR-10 12.31.28.000000 AM

10.小结
在Oracle数据中日期的操作相对复杂，如果可以善加利用与日期相关的函数，便会达到事半功倍的效果

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11269677/viewspace-1033351/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11269677/viewspace-1033351/