SQL> CREATE OR REPLACE FUNCTION FUN_CHECK_DATE(S_DATE VARCHAR2)
2 RETURN NUMBER IS
3 DT_TEMP DATE;
4 BEGIN
5 SELECT TO_DATE(S_DATE,'YYYY-MM-DD') INTO DT_TEMP FROM DUAL;
6 RETURN 1;
7 EXCEPTION WHEN OTHERS THEN
8 RETURN 0;
9 END;
10 /Function created.SQL> SELECT FUN_CHECK_DATE('2003-01-01') FROM DUAL;FUN_CHECK_DATE('2003-01-01')
----------------------------
1SQL> SELECT FUN_CHECK_DATE('2003-15-01') FROM DUAL;FUN_CHECK_DATE('2003-15-01')
----------------------------
0SQL>
2 RETURN NUMBER IS
3 DT_TEMP DATE;
4 BEGIN
5 SELECT TO_DATE(S_DATE,'YYYY-MM-DD') INTO DT_TEMP FROM DUAL;
6 RETURN 1;
7 EXCEPTION WHEN OTHERS THEN
8 RETURN 0;
9 END;
10 /Function created.SQL> SELECT FUN_CHECK_DATE('2003-01-01') FROM DUAL;FUN_CHECK_DATE('2003-01-01')
----------------------------
1SQL> SELECT FUN_CHECK_DATE('2003-15-01') FROM DUAL;FUN_CHECK_DATE('2003-15-01')
----------------------------
0SQL>
/
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 Package.
Create Public Synonym for package. To use in a SQL statement: Select DATE_CONV.is_date(flex_field1) from flex_table; or Select DATE_CONV.is_date(flex_field1, "YYYY-MM-DD-HH:MI:SS") from flex_table;
return number
as
a date;
begin
a:=to_date(p_date,'yyyy-mm-dd');
return 1;
exception
when others then
return 0;
end;
/