Not a valid month error in TO_Char to Date line
In my oracle query, I'm getting a not a valid month while using the following line of code:
TO_CHAR(TO_DATE(TRIM(D.MOSTRECENTHIRE),'yyyymmdd'),'yyyy-mm-dd') AS HIREDATE, I've tried using:
TO_CHAR(TO_DATE(NULLIF(D.MOSTRECENTHIRE, '00000000'), 'yyyymmdd'), 'yyyy-mm-dd')But that didn't work either. Suggestions on how I can resolve this?
Alternately, you could subtitute a dummy value instead of NULL and then look for those values.
to_char(to_date(TRIM(D.MOSTRECENTHIRE) default '00010101' on conversion error,'yyyymmdd'),'yyyy-mm-dd')
Using '00000000' as a "default" date will generate that error whenever MOSTRECENTHIRE is NULL.
The NULLIF function doesn't substitute the '00000000' string when the column is null,
instead, it returns NULL when the column value = '00000000'
you could get fancy by checking validation and then returning either a formatted date string or an error message depending on the validation results.
CASE
WHEN validate_conversion(TRIM(d.mostrecenthire) AS DATE, 'yyyymmdd') =
1
THEN
TO_CHAR(TO_DATE(TRIM(d.mostrecenthire), 'yyyymmdd'),
'yyyy-mm-dd')
ELSE
'Error converting value: ' || d.mostrecenthire
END most_recent_hire
Using '00000000' as a "default" date will generate that error whenever MOSTRECENTHIRE is NULL.
You'll want to check the value with a CASE statement and return your default value when NULL, otherwise make the conversion.