Oracle数据库中日期格式的转换

Oracle数据库中日期格式的转换

1. 问题由来

从Oracle中读取日期字段，出现的日期格式相当反人类。所以必须转化一下。并且php的date(‘Y-m-d’,strtotime($date_str))虽然功能强大，但是strtotime()只能将英文文本的日期时间描述解析为 UNIX 时间戳，然后转化为格式化后的date字符串。

2. 解决方案

捣鼓了很久。我对Oracle还是一个萌新，本来是想有什么法子可以直接设置Oracle 默认日期显示格式。
试过了N多种方法，都没有成功。不得不感叹Oracle比MySQL要麻烦得多啊~
下面介绍几种在客户端修改显示方式的方法。
1. 直接用PHP正则表达式来实现转化。

/**
 * 将 '11-12月-16 01.53.16.674000 上午 +08:00' 
 * 转化为 '2016-12-11 01:53:16'
 */
function timeFormat($src) {
    $reg='/(?<d>\d{2})-(?<m>\d{1,2})月\s*-(?<y>\d{2})\s*(?<h>\d{1,2}).(?<i>\d{1,2}).(?<s>\d{1,2})[.\d\s]*(?<z>.*?)午.*?/';
    preg_match($reg,$src,$m);
    if($m['z'] == '下') $m['h'] += 12;
    return date('Y-m-d H:i:s',strtotime($m['y'].'-'.$m['m'].'-'.$m['d'].' '.$m['h'].':'.$m['i'].':'.$m['s']));
}

1. 另外， 对于SQLPlus我们可以通过如下命令，修改当前会话的日期格式（只对当前客户端有效）：

alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

1. 在SQL Developer中，我们可以在“工具=》首选项=》数据库=》NLS参数”中修改显示格式（只对当前客户端有效）：
   

3. 反面教材：

下面是我的捣鼓的过程。不知道哪里不对，如果大牛能够指正就更好了。

1. 根据官方的提示，我尝试过，设置Logon登录触发器，如下：

CREATE OR REPLACE
TRIGGER TRIG_CHANGE_DATE_FORMAT
AFTER LOGON OR SERVERERROR OR STARTUP ON DATABASE 
BEGIN
--  EXECUTE IMMEDIATE 'ALTER SESSION SET  NLS_DATE_FORMAT = ''YYYY-MM-DD HH24:MI:SS''';
  DBMS_SESSION.SET_NLS('NLS_DATE_FORMAT','"YYYY-MM-DD HH24:MI:SS"');
  COMMIT;
END TRIG_CHANGE_DATE_FORMAT;

但是，没有任何反应。
2. 然后，也尝试了设置regedit, 开始=》运行=》regedit=》HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\KEY_OraDb11g_home1，新增字符串’NLS_DATE_FORMAT’值为’YYYY-MM-DD-HH24:MI:SS’，依旧没有任何反应。

3. 尝试使用下面命令，依旧不起作用。

alter system set NLS_DATE_FORMAT='YYYY-MM-DD:HH24:MI:SS' scope=spfile;

4. 参考链接

1. http://www.dba-oracle.com/t_nls_date_format.htm
2. http://stackoverflow.com/questions/22860101/how-can-i-alter-nls-date-format-in-php
3. http://www.dba-oracle.com/t_change_nls_date_format_login_trigger.htm
4. http://www.oracle.com/technetwork/articles/fuecks-dates-098686.html
5. http://blog.csdn.net/haibusuanyun/article/details/11181577