TO_DATE ignores

From: Fergal Taheny <ftaheny_at_gmail.com>
Date: Thu, 21 Feb 2013 12:45:29 +0000
Message-ID: <CAOuMUT5pPCaKkRKdc8nK+9gEb0=0BUcA0FJF=hYF6qi8YHxdwQ_at_mail.gmail.com>

Hi,
Tried this on 10.2.0.4 and 11.1.0.7

I guess I'm not the first to stumble across this.

I'm extracting data from an application where some dates are stored free form in varchars (I know. Vendor supplied application.)

So I wrote a simple proc to identify incorrectly entered dates (invalid format). I pass in a date and format string and the proc tries to do a to_date. if successful return Y and if unsuccessful return N.

 FUNCTION f_isdate (p_date IN VARCHAR,p_format IN VARCHAR) RETURN VARCHAR2 DETERMINISTIC
  IS
  v_date DATE;
  BEGIN

• This function attempts to convert the date supplied using the format supplied and returns 'Y'
• if the date is valid and 'N' if it is invalid.
• null is considered a valid date and return a 'Y'

        SELECT TO_DATE(p_date, p_format)
          INTO v_date
          FROM dual;
     RETURN 'Y';

  EXCEPTION
    WHEN OTHERS THEN -- if we can't to_date the input, its not a date

       RETURN 'N';
  END f_isdate;

My assumption was that to_date would fail if char and fmt were not in the same format. But this assumption seems to be incorrect.

I expected that these would all fail but they didn't:

12:47:59 SQL*Plus > select to_date('01-JAN-01','DD/MM/YYYY') as the_date from dual;

THE_DATE

---

01-JAN-01 00:00 12:48:09 SQL*Plus > select to_date('01/JAN/01','DD-MON-YYYY') as the_date  from dual;

THE_DATE

---

01-JAN-01 00:00 12:48:28 SQL*Plus > select to_date('01/JAN/01','DD-MON-YYYY') as the_date from dual;

THE_DATE

---

01-JAN-01 00:00 to_timestamp does the same:

SELECT TO_TIMESTAMP('01/JAN/01','DD-MON-YYYY') as the_date from dual;

THE_DATE

---

01-JAN-01 00:00:00.000000000 select * from V$nls_parameters;

PARAMETER                                                        VALUE
----------------------------------------------------------------
------------------------------
NLS_LANGUAGE                                                     ENGLISH
NLS_TERRITORY                                                    IRELAND
NLS_CURRENCY                                                     ?
NLS_ISO_CURRENCY                                                 IRELAND
NLS_NUMERIC_CHARACTERS                                           .,
NLS_CALENDAR                                                     GREGORIAN
NLS_DATE_FORMAT                                                  DD-MON-YY
HH24:MI
NLS_DATE_LANGUAGE                                                ENGLISH
NLS_CHARACTERSET                                                 US7ASCII
NLS_SORT                                                         BINARY

NLS_TIME_FORMAT
 HH12:MI:SSXFF AM

NLS_TIMESTAMP_FORMAT                                             DD-MON-RR

HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT
HH12:MI:SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT                                          DD-MON-RR
HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY                                                ?
NLS_NCHAR_CHARACTERSET                                           AL16UTF16
NLS_COMP                                                         BINARY
NLS_LENGTH_SEMANTICS                                             BYTE
NLS_NCHAR_CONV_EXCP                                              FALSE

Any ideas?

Regards,
Fergal

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Feb 21 2013 - 13:45:29 CET