是别人写的: 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 ASFUNCTION 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; /
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;
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 ASFUNCTION 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;
/
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;