注意
- trunc不支持TIMESTAMP数据类型。
- 先执行命令:alter session set nls_date_format=’yyyy-mm-dd hh24:mi:ss’;
截取今天:
select sysdate,trunc(sysdate,'dd') t_t from dual;
根据小时四舍五入到今天或明天:
select sysdate,round(sysdate,'dd') t_t from dual;
截取本周第一天:
select sysdate,trunc(sysdate,'d') t_t from dual;
-- 一周的第一天是星期天,亲,为啥,问西方的上帝去
筛选出日期列值在周四和周五之间的记录:
select to_char(trunc(t.tradetime, 'd') + 4, 'yyyy-mm-dd hh24:mi:ss'),
to_char(t.tradetime, 'yyyy-mm-dd hh24:mi:ss'),
to_char(trunc(t.tradetime, 'd') + 5, 'yyyy-mm-dd hh24:mi:ss')
from t
where trunc(t.tradetime, 'd') + 5 - t.tradetime between 0 and 1;
截取本月第一天:
select sysdate,trunc(sysdate,'mm') t_t from dual;
截取本年第一天:
select sysdate,trunc(sysdate,'y') t_t from dual;
截取到小时:
select sysdate,trunc(sysdate,'hh') t_t from dual;
根据当前的分钟四舍五入到小时:
select round(to_date('2018-01-08 12:29','yyyy-mm-dd hh24:mi'),'hh') t_t from dual;
select round(to_date('2018-01-08 12:30','yyyy-mm-dd hh24:mi'),'hh') t_t from dual;
截取到分钟:
select sysdate,trunc(sysdate,'mi') t_t from dual;
根据秒四舍五入到分钟:
select round(to_date('2018-01-08 12:29:29','yyyy-mm-dd hh24:mi:ss'),'mi') t_t from dual;
select round(to_date('2018-01-08 12:29:30','yyyy-mm-dd hh24:mi:ss'),'mi') t_t from dual;
[TOC]