Oracle date操作

1. date常规的操作(请注意,常规不代表oracle提供date操作的全部)
1.1. date转换为字符串
我们总是想看到日期和时间是什么样的(希望按照我们指定的格式显示), 因此总是需要把它to_char

代码:
   
   


SQL> select to_char(sysdate,'yyyy"(year)"mm"(month)"dd"(day) "hh24"(hour)"mi"(minute)"ss"(second)"') as mydate from dual;



MYDATE

-------------------------------------------------------

2006(year)01(month)04(day) 10(hour)58(minute)27(second)

  
  

正如上面的例子, 除了oracle提供的Date Format, 我们可以通过双引号添加自己希望看到的间隔符,
当间隔符是oracle可以自动识别的符号的时, 可以省去双引号, 如

代码:
   
   


SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') as mydate from dual;



MYDATE

-------------------

2006/01/04 11:03: 
  
  

但是这里还是建议分割符都用双引号, 提高可读性,兼容性和易维护性.
这里要提醒大家的是, 自定义的分割符不要太长, 如果太长, oracle会报错
(ORA-01801: date format is too long for internal buffer), 有兴趣可以试试

1.2. date的默认格式
最近总是看到有人会直接用日期字符串插入到date字段中的sql, 我的建议是最好不要,
因为这里面涉及到一个oracle隐式转换的问题,
oracle的答复是允许隐式转换, 但并不推荐使用
这里, date的隐式转换是依赖于默认的日期格式nls_date_format(可能还要考虑nls_language),
下面我们来看下面的代码

代码:
   
   


--首先, 我们创建一个表

create table d (d date);

--然后我们看一下当前的nls_date_format和nls_language

SQL> select * from v$nls_parameters where parameter in ('NLS_DATE_FORMAT','NLS_LANGUAGE');



PARAMETER          VALUE

------------------ --------------------

NLS_LANGUAGE       SIMPLIFIED CHINESE

NLS_DATE_FORMAT    DD-MON-RR



--好了, 我们来利用这些, 来试试日期的隐式转换

--习惯性的日期格式'yyyy-mm-dd'

SQL> insert into d values('2006-01-01');



insert into d values('2006-01-01')



ORA-01861: literal does not match format string



--不考虑language的情况下来插入数据

SQL> insert into d values('01-jan-06');



insert into d values('01-jan-06')



ORA-01843: not a valid month



--NLS_LANGUAGE 和 NLS_DATE_FORMAT 都考虑的情况下

SQL> insert into d values('01-1月-06');



1 row inserted



SQL> commit;




  
  

上面三次插入数据, 只有最后一次才成功, 因为它的格式符合隐式转换要求的格式, 但是大家觉得这样好吗?
不同的oracle环境, 这些参数都是不一样的, 如果用隐式转换, 显然带来的是隐患, 更直接一点就是错误.

不过, 这里我要说一下, 是可以通过alter session set NLS_LANGUAGE='&date_format'来改变当前session的日期格式,
或是把oracle的默认日期格式改掉, 但是大家觉得这样好吗?
假如你没有权利修改oracle的参数或是不方便来修改参数呢?还要坚持用隐式转换吗?

1.3. to_char(date,'date_format'), oracle提供的其他date_format我这里就不赘述了, 这个是to_char函数使用的基础.
如果觉得忘却了, 可以参见oracle doc(%ora10g doc%/server.102/b14200.pdf, %ora9i doc%/server.920/a96540.pdf),
学会看oracle doc是很重要的

2. date 其他操作
2.1 关于日期的比较
日期是可以直接做比较的, 比如 sysdate>to_date('20040101','yyyymmdd')
2.2 日期的加减
日期在加减的时候, 我们可以把日期看成是数字, 整数部分代表年月日, 小数部分代表时分秒

代码:
   
   


如: 当前时间加2天 sysdate+2,

    前3天半的时间 sysdate-3.5

    两个日期相差多长时间 datetime1-datetime2 , 这里要注意, 得到的是一个Interval(看着是数字) , 不再
  
  

2.3 日期时间的特定加减
现在有朋友提出需要单独的增加几年, 或几月, 或几个小时, 或几分钟, 几秒
那么最好通过 datetime + interval 'n' year/month/hour/munite/second 来实现
也许会有朋友说, 加年可以通过 add_months(datetime, n*12), 加月 add_months(datetime, n),
加小时 datetime+n/24 等等, 我个人的看法是可以, 但是不好, 显然用interval的可读性和易维护性要比后面的方法要好的多,
而且在不出现除法的可能性下最好不要出现诸如 n/24, n/(24*60)之类的增加小时或分的操作

代码:
   
   


--创建表

SQL> create table test1(d date, c char);



Table created



--插入10条日期数据, 数据间隔为1个小时

SQL> insert into test1 select trunc(sysdate)+rownum/24,'h' from dual connect by level<=10;



10 rows inserted



--用interval如何实现

SQL> insert into test1 select trunc(sysdate)+ (interval '1' hour)*rownum, 'i' from dual connect by level<=10;



10 rows inserted



SQL> commit;




  
  

interval 除了可以单独增加某个时间单位的间隔外, 还可以是组合的日期
如: interval 'expr' day/hour/munite/second to day/hour/munite/second
interval 'expr' year/month to year/month

代码:
   
   


-- interval hour to second 示例

SQL> select to_char(sysdate, 'hh:mi:ss') as curr_date, to_char(sysdate+interval '1:20:30' hour to second, 'hh:mi:ss') as next_date from dual;



CURR_DATE NEXT_DATE

--------- ---------

03:45:30  05:06: