日期轉換為raw的函式

SQL> create or replace function date_to_raw(rv varchar2) return varchar2
2 as
3 rv_century number;
4 rv_year number;
5 rv_month number;
6 rv_day number;
7 rv_hour number;
8 rv_mil number;
9 rv_sec number;
10 begin
11 rv_century:=to_number(substr(rv,1,2))+100;
12 rv_year:=to_number(substr(rv,3,2))+100;
13 rv_month:=to_number(substr(rv,5,2));
14 rv_day:=to_number(substr(rv,7,2));
15 rv_hour:=to_number(substr(rv,9,2))+1;
16 rv_mil:=to_number(substr(rv,11,2))+1;
17 rv_sec:=to_number(substr(rv,13,2))+1;
18
19 return lpad(trim(to_char(rv_century,'xx')),2,'0')||
20 lpad(trim(to_char(rv_year,'xx')),2,'0')||
21 lpad(trim(to_char(rv_month,'xx')),2,'0')||
22 lpad(trim(to_char(rv_day,'xx')),2,'0')||
23 lpad(trim(to_char(rv_hour,'xx')),2,'0')||
24 lpad(trim(to_char(rv_mil,'xx')),2,'0')||
25 lpad(trim(to_char(rv_sec,'xx')),2,'0');
26 end date_to_raw;
27 /

函式已建立。

SQL> select date_to_raw('20100607070000') from dual;

DATE_TO_RAW('20100607070000')
--------------------------------------------------------------------------------

786e0607080101

SQL> SET SERVEROUTPUT ON
SQL> declare
2 rv RAW(32) := '786E0607080101';
3 dt DATE := NULL;
4 BEGIN
5 dbms_stats.convert_raw_value(rv,dt);
6 dbms_output.put_line(to_char(dt,'YYYY-MM-DD HH24:MI:SS'));
7 END;
8 /
2010-06-07 07:00:00

PL/SQL 過程已成功完成。