工作中使用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

sql monitor中timestamp變數轉換可識別格式

相關文章