实验环境 ： bys@bys1select * from nls_session_parameters where parameter='nls_date_format'; parameter value -------------------- ------------------------------ nls_date_format yyyy/mm/dd hh24:mi:ss bys@bys1show parameter nls_lang name type
实验环境：
bys@bys1>select * from nls_session_parameters where parameter='nls_date_format';
parameter value
-------------------- ------------------------------
nls_date_format yyyy/mm/dd hh24:mi:ss
bys@bys1>show parameter nls_lang
name type value
------------------------------------ ----------- ------------------------------
nls_language string american
1.直接使用sysdate加减数字来操作日期日期+或-1,都代表加减一天的时间；而如果是一小时或几分钟这种，可以用天/小时这种方法。
如下面语句，1小时是1/24；5分钟是1/24/12。86400：1天=24小时=24*60*60=86400秒
bys@bys1>select sysdate+365,sysdate-1,sysdate-3,sysdate-1/24,sysdate-1/24/12 from dual;
sysdate+365 sysdate-1 sysdate-3 sysdate-1/24 sysdate-1/24/12
------------------- ------------------- ------------------- ------------------- -------------------
2014/11/02 19:26:15 2013/11/01 19:26:152013/10/30 19:26:15 2013/11/0218:26:15 2013/11/0219:21:15
2.timestamp 记录了年、月、日、时、分、秒和纳秒systimestamp返回的是timestamp with time zone 类型的数据。+08:00表示当前是东八区。
bys@bys1>select systimestamp from dual;
systimestamp
---------------------------------------------------------------------------
02-nov-13 09.08.04.390741 pm +08:00
timestamp的显示格式不同于sysdate，要重新指定。
bys@bys1>alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss.ff';
session altered.
bys@bys1>select systimestamp from dual;
systimestamp
---------------------------------------------------------------------------
02-nov-13 09.11.19.258161 pm +08:00
表示timestamp的方法：
–to_timestamp('2013-02-09 23:59:59.000','yyyy-mm-dd hh24:mi:ss.ff')
–timestamp '2013-04-05 13:48:00.123456789'
–to_timestamp中的分隔符可以更换， timestamp中的日期分隔符必须是-，时间必须是:，秒后面必须跟上.
–timestamp可以精确表示到毫秒、微秒甚至纳秒级别
转换时未指定值时的默认值：年：同sysdate里的年；月：同sysdate里的月；日：1号；时分秒和纳秒：均为0
bys@bys1>col a3 for a30
bys@bys1>col a2 for a30
bys@bys1>col a1 for a30
bys@bys1>select to_timestamp('05 13','yy hh24') as a1,to_timestamp('05 13','mm mi') as a2,to_timestamp('05 13','dd ss') as a3 from dual;
a1 a2 a3
------------------------------ ------------------------------ ------------------------------
2005-11-01 13:00:00.000000000 2013-05-01 00:13:00.000000000 2013-11-05 00:00:13.000000000
关于微秒的指定方式：ff5表示给的时间戳可以有不超过5位的微秒。如果时间戳微秒有3位，指定转换为ff2，则报错。
同时在秒后最多只能指定9位。
bys@bys1>select to_timestamp('05 13:48:22.778','dd hh24:mi:ss.ff5') from dual;
to_timestamp('0513:48:22.778','ddhh24:mi:ss.ff5')
---------------------------------------------------------------------------
2013-11-05 13:48:22.778000000
要注意
bys@bys1>select to_timestamp('05 13:48:22.778','dd hh24:mi:ss.ff2') from dual;
select to_timestamp('05 13:48:22.778','dd hh24:mi:ss.ff2') from dual
*
error at line 1:
ora-01880: the fractional seconds must be between 0 and 999999999
bys@bys1>select to_timestamp('05 13:48:22.123456789','dd hh24:mi:ss.ff9') from dual;
to_timestamp('0513:48:22.123456789','ddhh24:mi:ss.ff9')
---------------------------------------------------------------------------
2013-11-05 13:48:22.123456789
bys@bys1>select to_timestamp('05 13:48:22.1234567890','dd hh24:mi:ss.ff9') from dual;
select to_timestamp('05 13:48:22.1234567890','dd hh24:mi:ss.ff9') from dual
*
error at line 1:
ora-01830: date format picture ends before converting entire input string
bys@bys1>select to_timestamp('05 13:48:22.1234567890','dd hh24:mi:ss.ff10') from dual;
select to_timestamp('05 13:48:22.1234567890','dd hh24:mi:ss.ff10') from dual
*
error at line 1:
ora-01821: date format not recognized
3.date函数只可以表示日期，不可以表示时间。在下面4中有应用示例。默认值：年：同sysdate里的年；月：同sysdate里的月；日：1号；时分秒：均为0
4.判断指定日期是否是某一天的。to_date及date中如果只指定日期未指定时间，默认是0点0分0秒。即前一天23:59:59的下一秒。注意between and 相当于大于等于和小于等于。所以属于某一天，严格来说应该是从当天0点的0秒到 当天23:59:59秒。1天除以86400即1秒
bys@bys1> select 'true' from dual where to_date('2013-11-02 21:48:22','yyyy-mm-dd hh24:mi:ss') between date'2013-11-01' and date'2013-11-06'-1/86400;
'tru
----
true
bys@bys1>select 'true' from dual where to_date('2013/11/02 21:45:43','yyyy-mm-dd hh24:mi:ss') between to_date('2013-11-02','yyyy-mm-dd') and to_date('2013-11-03','yyyy-mm-dd hh24:mi:ss')-1/86400;
'tru
----
true
也可以用to_date对日期进行显式转换。
select 'true' from dual where to_date('2013/11/02 21:45:43','yyyy-mm-dd hh24:mi:ss') between to_date('2013-11-02','yyyy-mm-dd') and to_date('2013-11-03','yyyy-mm-dd hh24:mi:ss')-1/86400;
'true'
------
true
注意between and 相当于大于等于和小于等于
bys@bys1> select 'true' from dual where to_date('2013/11/02 21:45:43','yyyy-mm-dd hh24:mi:ss') >= date'2013-11-02' and to_date('2013/11/02 21:45:43','yyyy-mm-dd hh24:mi:ss') 'tru
----
true
其实也可以用小于11月3号来表示小于等于11月2号的23：59：59秒。
bys@bys1> select 'true' from dual where to_date('2013/11/02 23:59:59','yyyy-mm-dd hh24:mi:ss') >= date'2013-11-02' and to_date('2013/11/02 23:59:59','yyyy-mm-dd hh24:mi:ss') 'tru
----
true
5.months_between(a,b):表示a和b两个日期的月份之差，是a-b，如果a日期比b晚，即比b大，则为正数；反之，为负数。bys@bys1>select empno,hiredate,months_between(sysdate,hiredate)/12 dday,sysdate from emp where rownum empno hiredate dday sysdate
---------- ------------------- ---------- -------------------
7369 1980/12/17 00:00:00 32.8784294 2013/11/02 18:37:05
7499 1981/02/20 00:00:00 32.7036983 2013/11/02 18:37:05
bys@bys1>select months_between(sysdate,to_date('2011/11/11 11:11:11','yyyy-mm-dd hh24:mi:ss')) as dday from dual;
dday
----------
23.7196307
bys@bys1>select months_between(to_date('2011/11/11 11:11:11','yyyy-mm-dd hh24:mi:ss'),sysdate) as dday from dual;
dday
----------
-23.719639
6.add_months:表示给指定的日期加一个月数，即n个月后的日期。如果当前日期加上指定月数超过一年，则年份也自动增加。bys@bys1>select add_months(sysdate,1),add_months(sysdate,4) from dual;
add_months(sysdate, add_months(sysdate,
------------------- -------------------
2013/12/02 18:39:23 2014/03/02 18:39:23
7.next_day:表示以当前时间为基准,下一个目标日的日期bys@bys1>select next_day(sysdate,'sunday'),next_day(sysdate,'tuesday') from dual;
next_day(sysdate,'s next_day(sysdate,'t
------------------- -------------------
2013/11/03 19:34:20 2013/11/05 19:34:20
8.last_day:计算当前日期的最后一天，即当月最后一天。bys@bys1>select last_day(sysdate) from dual;
last_day(sysdate)
-------------------
2013/11/30 18:43:16
9.使用round:对日期进行四舍五入只能对年、月、日、时、分进行四舍五入；不能操作秒。
bys@bys1>select round(sysdate,'yy') as year,round(sysdate,'mm') as month,round(sysdate,'dd') as day,round(sysdate,'hh') as hour,round(sysdate,'hh24') as hour24,round(sysdate,'mi') as minutes from dual;
year month day hour hour24 minutes
------------------- ------------------- ------------------- ------------------- ------------------- -------------------
2014/01/01 00:00:00 2013/11/01 00:00:00 2013/11/03 00:00:00 2013/11/02 19:00:00 2013/11/02 19:00:00 2013/11/02 18:59:00
bys@bys1> select round(sysdate,'ss') as sss from dual;
select round(sysdate,'ss') as sss from dual
*
error at line 1:
ora-01899: bad precision specifier
10.使用trunc:对日期进行截取bys@bys1>set linesize 200
bys@bys1>select trunc(sysdate,'yy') as year,trunc(sysdate,'mm') as month,trunc(sysdate,'dd') as day,trunc(sysdate,'hh') as hour,trunc(sysdate,'hh24') as hour24,trunc(sysdate,'mi') as minutes from dual;
year month day hour hour24 minutes
------------------- ------------------- ------------------- ------------------- ------------------- -------------------
2013/01/01 00:00:00 2013/11/01 00:00:00 2013/11/02 00:00:00 2013/11/02 18:00:00 2013/11/02 18:00:00 2013/11/02 18:52:00
只能截取年、月、日、时、分；不能截取秒。
bys@bys1> select trunc(sysdate,'ss') as sss from dual;
select trunc(sysdate,'ss') as sss from dual
*
error at line 1:
ora-01899: bad precision specifier