使用TRUNC函数可以完成时间的截取功能。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
--转自
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2015-6-25 14:52:45 |
赞(
操作
