The function is very simple: i just try to convert variable to date, and if i can then variable is date.
SQL> CREATE OR REPLACE CREATE OR REPLACE FUNCTION IS_DATE ( a_date IN VARCHAR2) RETURN VARCHAR2 IS v_result VARCHAR2 (1 CHAR) := 'Y'; not_date EXCEPTION; non_numeric EXCEPTION; v_date DATE; PRAGMA EXCEPTION_INIT( not_date , -1861); PRAGMA EXCEPTION_INIT( non_numeric , -1858); BEGIN BEGIN SELECT TO_DATE(a_date) INTO v_date FROM DUAL; EXCEPTION WHEN not_date THEN v_result := 'N'; WHEN non_numeric THEN v_result := 'N'; END; RETURN v_result; END IS_DATE;
And that's how it works:
SQL> SELECT IS_DATE (sysdate) A FROM DUAL; A -------------------------------------------------------------------------------- Y SQL> SELECT IS_DATE ('01.06.2011') A FROM DUAL; A -------------------------------------------------------------------------------- Y SQL> SELECT IS_DATE ('THIS DAY') A FROM DUAL; A -------------------------------------------------------------------------------- N
But be careful, because function uses current datemask:
SQL> SELECT VALUE FROM NLS_SESSION_PARAMETERS 2 WHERE PARAMETER = 'NLS_DATE_FORMAT' 3 ; VALUE -------------------------------------------------------------------------------- dd.mm.yyyy hh24:mi:ss SQL> SELECT IS_DATE ('2011-06-01') A FROM DUAL; A -------------------------------------------------------------------------------- Y
Note that
SQL> SELECT TO_DATE ('2011-06-01') A FROM DUAL; A ------------------- 20.11.0006 01:00:00
No comments:
Post a Comment