2011/06/01

is date function in Oracle

Sametimes i use this function to know, is a type of variable or field is date or not.
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