What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Oracle’s VALIDATE_CONVERSION tests whether an expression can be converted to a specified data type: it returns 1 when conversion is possible and 0 when it is not. A null expression also returns 1, so check for null separately when a value is required. The function validates convertibility; it does not produce the converted value.
What VALIDATE_CONVERSION does
VALIDATE_CONVERSION is a SQL function for checking a conversion before applying a corresponding conversion such as TO_DATE or TO_NUMBER. Oracle documents the function in the Oracle Database 19c SQL Language Reference.
Its general syntax is:
VALIDATE_CONVERSION(expr AS type_name [, fmt [, nlsparam]])expris the expression to test.type_nameis the target type.fmt, when applicable, supplies a format model for interpreting character input.nlsparam, when applicable, supplies relevant NLS settings, such as date language or numeric characters.
If Oracle encounters an error while evaluating expr itself, the function returns that error rather than converting it into a zero result. This distinction matters when the expression contains operations that can fail independently of the conversion check.
Which types can it validate?
Oracle documents these target types: BINARY_DOUBLE, BINARY_FLOAT, DATE, INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH, NUMBER, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE. The accepted text follows the conversion rules for the target type: for example, date and number input can depend on format models and NLS settings. Interval targets use SQL interval or ISO duration formats; Oracle documents no fmt or nlsparam argument for those targets. See the type and conversion details in Oracle’s reference.
Recommended Free Tools
#1 Best Overall
Check staged data before converting it
A common use is filtering staging rows so that only values accepted by their intended format models reach the conversion functions. Oracle’s SQL development guidance demonstrates this validation-and-conversion pattern for loading data (Oracle SQL data type guidance).
INSERT INTO annual_sales (created_date, amount)
SELECT TO_DATE(created_date, 'dd-mon-yyyy'),
TO_NUMBER(amount, '999999D99')
FROM staging_sales
WHERE VALIDATE_CONVERSION(created_date AS DATE, 'dd-mon-yyyy') = 1
AND VALIDATE_CONVERSION(amount AS NUMBER, '999999D99') = 1;Use the same format model for validation and conversion. If the masks differ, a value might pass validation under one interpretation but fail when the subsequent conversion uses another.
Handle text that can use more than one date format
When a source column contains dates in several known formats, test each accepted format and convert with the corresponding mask:
CASE
WHEN VALIDATE_CONVERSION(raw_date AS DATE, 'yyyymmdd') = 1
THEN TO_DATE(raw_date, 'yyyymmdd')
WHEN VALIDATE_CONVERSION(raw_date AS DATE, 'dd/mm/yyyy') = 1
THEN TO_DATE(raw_date, 'dd/mm/yyyy')
WHEN VALIDATE_CONVERSION(raw_date AS DATE, 'dd-mon-yyyy') = 1
THEN TO_DATE(raw_date, 'dd-mon-yyyy')
ENDThe branches make the accepted formats explicit and pair each successful check with its matching conversion. If no branch matches, the CASE expression returns null because it has no ELSE clause; add one if the query needs a different fallback. Oracle’s release article illustrates multi-mask validation and shows '123a' failing a number check while '123' passes (Oracle: converting strings to numbers and dates).
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Set format and NLS rules explicitly when needed
For text whose interpretation depends on language or numeric punctuation, include a format model and the relevant NLS parameter. Oracle’s examples include a spelled-out English month and a number using a comma decimal mark:
SELECT VALIDATE_CONVERSION(
'July 20, 1969, 20:18' AS DATE,
'Month dd, YYYY, HH24:MI',
'NLS_DATE_LANGUAGE = American'
)
FROM dual;
SELECT VALIDATE_CONVERSION('$100,00' AS NUMBER,
'$999D99',
'NLS_NUMERIC_CHARACTERS = '',.''')
FROM dual;These examples return 1 when the supplied format and NLS settings match the input. The format is part of what is being validated: Oracle’s reference also shows '$29.99' failing as BINARY_FLOAT with default parsing and passing when a '$99D99' format model is supplied (Oracle’s examples and syntax).
Account for nulls and use the conversion result
Because a null expression returns 1, a successful validation does not prove that a value is present. Require both convertibility and presence when filtering:
WHERE raw_amount IS NOT NULL
AND VALIDATE_CONVERSION(raw_amount AS NUMBER, '999999D99') = 1After validation, call the appropriate conversion function or use a cast to obtain the typed value. VALIDATE_CONVERSION reports whether conversion is possible; it does not replace TO_NUMBER, TO_DATE, or the relevant conversion operation. Its result can also depend on the format and NLS rules used, so make those explicit where input conventions require them.
Quick Recap
Best Value
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
