日期转换为raw的函数

在论坛上看到有需要日期转raw的兄弟，写了个函数：

create or replace function date_to_raw(rv varchar2) return varchar2
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;
/

[@more@]

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/