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