工作中使用SQL MONITOR分析变量时,若有TIMESTAMP格式,会出现“ 78740616010101 ” 无法识别的数字,通过下面方法可转换能识别的时间格式。
Binds
========================================================================================================================
| Name | Position | Type | Value |
========================================================================================================================
| :1 | 1 | NUMBER | 42599677 |
| :2 | 2 | TIMESTAMP | 78740616010101 |
| :3 | 3 | TIMESTAMP | 78740616010101 |
| :4 | 4 | VARCHAR2(32) | 360 |
| :5 | 5 | NUMBER | 10000 |
========================================================================================================================
可通过下面两种方法解析变量,转换可识别的时间:
方法一:
- select to_timestamp(
- to_char( to_number( substr( p_str, 1, 2 ), 'xx' ) - 100, 'fm00' ) ||
- to_char( to_number( substr( p_str, 3, 2 ), 'xx' ) - 100, 'fm00' ) ||
- to_char( to_number( substr( p_str, 5, 2 ), 'xx' ), 'fm00' ) ||
- to_char( to_number( substr( p_str, 7, 2 ), 'xx' ), 'fm00' ) ||
- to_char( to_number( substr( p_str,9, 2 ), 'xx' )-1, 'fm00' ) ||
- to_char( to_number( substr( p_str,11, 2 ), 'xx' )-1, 'fm00' ) ||
- to_char( to_number( substr( p_str,13, 2 ), 'xx' )-1, 'fm00' ), 'yyyymmddhh24miss' )
- from (select '&raw_timestamp' p_str from dual)
- Enter value for raw_timestamp: 787406160101
- old 9: from (select '&raw_timestamp' p_str from dual)
- new 9: from (select '787406160101' p_str from dual)
-
- TO_TIMESTAMP(TO_CHAR(TO_NUMBER(SUBSTR(P_STR,1,2),'XX')-100,'FM00')||TO_CHAR
- ---------------------------------------------------------------------------
- 22-JUN-16 12.00.00.000000000 AM
方法二:
- set serverout on
- declare
- d1 date;
- begin
- dbms_stats.convert_raw_value(hextoraw('78740616010101'),d1);
- dbms_output.put_line('DATE: '||to_char(d1,'yyyy-mm-dd hh24:mi:ss'));
- end;
- DATE: 2016-06-22 00:00:00
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17086096/viewspace-2121037/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/17086096/viewspace-2121037/