常用日期型数据类型:date与TIMESTAMP 二者在数据库中的存储固定为7个字节。
1.
获取系统时间 select sysdate from dual; --时间格式取决于系统设置
将日期转字符:
select to_char(sysdate,'yy-mm-dd hh24:mi:ss') from dual; --显示:15-11-25 19:55:42
将字符转日期:
select to_date('2015-11-25 19:48:52','yyyy-mm-dd,hh24:mi:ss') from dual; --显示:2015/11/25 19:48:52
3.
求两个日期的间隔时间:返回的单位为天,小时及分秒会换算成小数
select date'2015-11-25'-date'2015-11-20' from dual;
--也可以借助数学函数
select floor(sysdate - to_date('20151120','yyyymmdd')) from dual;
4.oracle trunc截断日期函数
select sysdate,trunc(sysdate,'dd') from dual; 截取今天
select sysdate,trunc(sysdate,'d') from dual; 截取本周第一天
select sysdate,trunc(sysdate,'mm') from dual; 截取本月第一天
select id, active_date from table1
UNION
select 1, TO_DATE(null) from dual;
5.处理月份天数不定的办法
select to_char(add_months(last_day(sysdate) +1, -2), 'yyyymmdd'),last_day(sysdate) from dual
案例一
create table uu_test_1(
C1 DATE,
C2 TIMESTAMP); --能保存小数秒,默认为6位
insert into uu_test_1(c1,c2) values(date'2015-11-25',timestamp'2015-11-25 19:48:52');
insert into uu_test_1(c1,c2) values(to_date('2015-11-25 19:48:52','YYYY-MM-DD HH24:MI:SS'),
to_timestamp('2015-11-25 19:48:52','YYYY-MM-DD HH24:MI:SS.FF6'));
select * from uu_test_1;
案例二 C开发语句
char c_USER_NUM[30]={0};
char ACTIVE_DATE[10]={0};
char INACTIVE_DATE[10]={0};
EXEC SQL SELECT a.USER_NUM,to_char(a.ACTIVE_DATE,'yyyymmddhh24miss'),to_char(a.INACTIVE_DATE,'yyyymmddhh24miss')
into :c_USER_NUM,:ACTIVE_DATE,:INACTIVE_DATE
FROM t_test a
WHERE a.user_num = '123456';
http://blog.itpub.net/9827879/viewspace-1116421/
http://www.cnblogs.com/xiaobaihome/archive/2012/03/14/2395568.html