判断是否是日期类型的函数是什么?就像SQL SERVER中的 isdate()一样,谢谢

[supercdz]

判断是否是日期类型的函数是什么?就像SQL SERVER中的 isdate()一样,谢谢

[beckhambobo]

create function is_date(p_date in date)
return number
as
a date;
begin
a:=p_date;
return 1;
exception
when others then
return 0;
end;
/
select * from tabname where is_date(col)=1;

[supercdz]

UP

[welyngj]

oracle没有自己的函数，可以自己编写一个

[welyngj]

是别人写的：
CREATE OR REPLACE PACKAGE DATE_CONV AS

FUNCTION Is_Date(In_Date IN Varchar2) RETURN DATE;

FUNCTION Is_Date(In_Date IN VARCHAR2, DATE_FORMAT IN VARCHAR2) RETURN DATE;

END;
/


Create or replace package body DATE_CONV AS

FUNCTION Is_Date(In_Date IN Varchar2) RETURN DATE is

v_Date_Format Sys.Nls_Session_Parameters.Value%Type;
v_Date_Value DATE;

Invalid_Date EXCEPTION;
Date_Not_Recognized EXCEPTION;

PRAGMA EXCEPTION_INIT(Invalid_Date,-1858);
PRAGMA EXCEPTION_INIT(Date_Not_Recognized,-1821);

BEGIN
SELECT Value
INTO v_Date_Format
FROM Sys.Nls_Session_Parameters
WHERE Parameter = 'NLS_DATE_FORMAT';
Dbms_Output.Put_Line('Nls_Timestamp_Format = '||v_Date_Format);
SELECT TO_DATE(In_Date,V_Date_Format)
INTO v_Date_Value
FROM Dual;
RETURN v_Date_Value;
EXCEPTION
WHEN Invalid_Date THEN
RETURN NULL;
WHEN Date_Not_Recognized THEN
RETURN NULL;
WHEN OTHERS THEN
Dbms_Output.Put_Line('SQLCODE = '||SQLCODE);
Dbms_Output.Put_Line('SQLERRM = '||SQLERRM);
RETURN NULL;
END Is_Date;

FUNCTION Is_Date(In_Date IN VARCHAR2, DATE_FORMAT IN VARCHAR2) RETURN DATE is

v_Date_Value DATE;

Invalid_Date EXCEPTION;
Date_Not_Recognized EXCEPTION;

PRAGMA EXCEPTION_INIT(Invalid_Date,-1858);
PRAGMA EXCEPTION_INIT(Date_Not_Recognized,-1821);

BEGIN
Dbms_Output.Put_Line('Timestamp_Format = '||Date_Format);
SELECT TO_DATE(In_Date,Date_Format)
INTO v_Date_Value
FROM Dual;
RETURN v_Date_Value;
EXCEPTION
WHEN Invalid_Date THEN
Dbms_Output.put_Line('Invalid Date');
RETURN NULL;
WHEN Date_Not_Recognized THEN
Dbms_Output.put_Line('Date Not Recognized');
RETURN NULL;
WHEN OTHERS THEN
Dbms_Output.Put_Line('SQLCODE = '||SQLCODE);
Dbms_Output.Put_Line('SQLERRM = '||SQLERRM);
RETURN NULL;
END Is_Date;

END;
/

[baojianjun]

收藏，謝謝了