as
rv_century number;
rv_year number;
rv_month number;
rv_day number;
rv_hour number;
rv_mil number;
rv_sec number;
begin
rv_century:=to_number(substr(rv,1,2))+100;
rv_year:=to_number(substr(rv,3,2))+100;
rv_month:=to_number(substr(rv,5,2));
rv_day:=to_number(substr(rv,7,2));
rv_hour:=to_number(substr(rv,9,2))+1;
rv_mil:=to_number(substr(rv,11,2))+1;
rv_sec:=to_number(substr(rv,13,2))+1;
return lpad(trim(to_char(rv_century,'xx')),2,'0')||
lpad(trim(to_char(rv_year,'xx')),2,'0')||
lpad(trim(to_char(rv_month,'xx')),2,'0')||
lpad(trim(to_char(rv_day,'xx')),2,'0')||
lpad(trim(to_char(rv_hour,'xx')),2,'0')||
lpad(trim(to_char(rv_mil,'xx')),2,'0')||
lpad(trim(to_char(rv_sec,'xx')),2,'0');
end date_to_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 过程已成功完成。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/19423/viewspace-1035216/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/19423/viewspace-1035216/