I have a table defined as follows:
CREATE TABLE s1_miscellaneous_field_data
(
program_code char(3) not null,
table_key varchar2(75) not null,
misc_field_nbr number(5) not null
CONSTRAINT ckc_mfd_misc_field_nbr CHECK (misc_field_nbr between 1 and 24),
miscellaneous_field varchar2(250) not null,
CONSTRAINT pk_miscellaneous_field_data PRIMARY KEY (program_code, table_key, misc_field_nbr)
using index
tablespace smartsoft_index
)
ORGANIZATION HEAP
tablespace smartsoft_data
/

Sample data:
PCE 3678 1 1
TKT 2299 4 98765
TKT 3035 4 777
TKT 3034 4 777
TKT 2299 5 STRING
TKT 3035 5 Waterloo
TKT 3034 5 Waterloo
PCE 3678 3 2
PCE 3678 4 3
PCE 3695 4 12.0000
PCE 3678 5 Test8-1
SCE 3678 1 1
SCE 3678 3 2
SCE 3678 4 3
SCE 3695 4 12.0000
SCE 3678 5 Test8-1
OCE 3678 1 1
OCE 3678 3 2
OCE 3678 4 3
OCE 3695 4 12.0000
OCE 3678 5 Test8-1


My requirement is to determine if the 4th column: miscellaneous_field contains a valid date.

I was trying to use the ISDATE function as follows:

select isdate(miscellaneous_field) from s1_miscellaneous_field_data
but get this error:

ORA-00904: "ISDATE": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Error at Line: 22 Column: 8

I also tried this SQL:
SELECT TO_CHAR(mfd.miscellaneous_field,'YYYY-MM-DD')
FROM s1_miscellaneous_field_data mfd,
s1_miscellaneous_tables mt,
s1_miscellaneous_fields mf
LEFT OUTER JOIN s1_miscellaneous_field_data
ON s1_miscellaneous_field_data.program_code = mf.program_code
WHERE mt.table_name = 's1_contract'
AND mf.field_type = 'DAT'
AND mfd.misc_field_nbr = 1

but get this result:
ORA-01481: invalid number format model
01481. 00000 - "invalid number format model"
*Cause: The user is attempting to either convert a number to a string
via TO_CHAR or a string to a number via TO_NUMBER and has
supplied an invalid number format model parameter.
*Action: Consult your manual.

Any assistance would be appreciated.


Murray