CREATE OR REPLACE FUNCTION "SYSTEM"."COBVERTTODATE" ( dateString varchar2 ) return date is returnDate as begin select (to_date(substr(date,1,16),'mm/dd/yyyy hh:mi')+decode(substr(date,17,2),'AM',0,1/2)) into returnDate from dual; return returnDate; end;这个函数对么?怎么编译不过去呢?
CREATE OR REPLACE FUNCTION "SYSTEM"."COBVERTTODATE" ( dateString varchar2 ) return date is returnDate as begin select (to_date(substr(dateString,1,16),'mm/dd/yyyy hh:mi')+decode(substr(dateString,17,2),'AM',0,1/2)) into returnDate from dual; return returnDate; end;上面的代码贴错了
这样呢? CREATE OR REPLACE FUNCTION "SYSTEM"."COBVERTTODATE" ( dateString varchar2 ) return date is returnDate date; begin select (to_date(substr(dateString,1,16),'mm/dd/yyyy hh:mi')+decode(substr(dateString,17,2),'AM',0,1/2)) into returnDate from dual; return returnDate; end;
这回明白了 CREATE OR REPLACE FUNCTION "SYSTEM"."COBVERTTODATE" ( dateString varchar2 ) return date as returnDate date; begin select (to_date(substr(dateString,1,16),'mm/dd/yyyy hh:mi')+decode(substr(dateString,17,2),'AM',0,1/2)) into returnDate from dual; return returnDate; end;可是怎么调用呢?
alter session set NLS_DATE_LANGUAGE='AMERICAN';然后可以直接用to_date函数来转。
to_date(S_DATEVALUE, 'mm/dd/yyyy hh:mi:ss AM')如果必须忽略秒数,可以再用trunc处理下
trunc(to_date(S_DATEVALUE, 'mm/dd/yyyy hh:mi:ss AM'),'mi')
-------------------
2005-05-01 10:13:00SQL> select trunc(to_date('05-01-2005 12:13:12 AM','mm/dd/yyyy hh:mi:ss AM'),'mi') from dual;TRUNC(TO_DATE('05-0
-------------------
2005-05-01 00:13:00SQL> select trunc(to_date('05-01-2005 10:13:12 PM','mm/dd/yyyy hh:mi:ss AM'),'mi') from dual;TRUNC(TO_DATE('05-0
-------------------
2005-05-01 22:13:00SQL> select trunc(to_date('05-01-2005 12:13:12 PM','mm/dd/yyyy hh:mi:ss AM'),'mi') from dual;TRUNC(TO_DATE('05-0
-------------------
2005-05-01 12:13:00
to_date(substr(date,1,16),'mm/dd/yyyy hh:mi')+decode(substr(date,17,2),'AM',0,1/2)觉得还是能不改 日期的语言设置 就不改
02 7 2005 1:13AM
02 17 2005 10:13PM
dateString varchar2
)
return date is returnDate
as
begin
select (to_date(substr(date,1,16),'mm/dd/yyyy hh:mi')+decode(substr(date,17,2),'AM',0,1/2)) into returnDate from dual;
return returnDate;
end;这个函数对么?怎么编译不过去呢?
dateString varchar2
)
return date is returnDate
as
begin
select (to_date(substr(dateString,1,16),'mm/dd/yyyy hh:mi')+decode(substr(dateString,17,2),'AM',0,1/2)) into returnDate from dual;
return returnDate;
end;上面的代码贴错了
CREATE OR REPLACE FUNCTION "SYSTEM"."COBVERTTODATE" (
dateString varchar2
)
return date
is
returnDate date;
begin
select (to_date(substr(dateString,1,16),'mm/dd/yyyy hh:mi')+decode(substr(dateString,17,2),'AM',0,1/2)) into returnDate from dual;
return returnDate;
end;
CREATE OR REPLACE FUNCTION "SYSTEM"."COBVERTTODATE" (
dateString varchar2
)
return date
as
returnDate date;
begin
select (to_date(substr(dateString,1,16),'mm/dd/yyyy hh:mi')+decode(substr(dateString,17,2),'AM',0,1/2)) into returnDate from dual;
return returnDate;
end;可是怎么调用呢?
如果字符串是02 7 2005 1:13AM,可以只用to_date就可以了。
select to_date('02 7 2005 1:13AM','mm/dd/yyyy hh:miAM') from dual;
在decode处需要处理一下,
decode(substr(date,17,2),'AM',decode(substr(date,12,2),'12',-1/2,0),decode(substr(date,12,2),'12',0,1/2))