TO_DATE ignores
- From: Fergal Taheny <ftaheny@xxxxxxxxx>
- To: oracle-l-freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 21 Feb 2013 12:45:29 +0000
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
