Hi everyone.
I'm new here, happy to join this community. ^^
Maybe my question is too simple but I cannot find a satisfying explanation - using various AI tools too.
I'm facing a strange behavior about TO_DATE function and date conversion using mixing format - default NLS_DATE_FORMAT is DD-MON-RR - see screnshot at the bottom.
My case, a simple query - date has DataType DATE:
SELECT birthdate as MYDATE FROM anag WHERE person = 1;
So now I try "to convert” and somehow it is what I'm expected
SELECT birthdate as MYDATE, TO_DATE(birthdate, ‘YYYY-MM-DD’) DATE_CONVERTED FROM anag WHERE person = 1;
But if i tried with date greater than 31-DEC-31 (> 31 dicember 2031) an error occur
SELECT birthdate as MYDATE FROM anag WHERE person = 2;
SELECT birthdate as MYDATE, TO_DATE(birthdate, ‘YYYY-MM-DD’) DATE_CONVERTED FROM anag WHERE person = 2;
01858. 00000 - "a non-numeric character was found where a numeric was expected"
*Cause: The input data to be converted using a date format model was
incorrect. The input data did not contain a number where a number was
required by the format model.
*Action: Fix the input data or the date format model to make sure the
elements match in number and type. Then retry the operation.
I know it is a bad practice and wrong convert a date as above, but I'm looking for a technical explanation, I want to know what happen behind the scene, maybe debugging if possible. Why?
Doc reference, debug tech instruction are apprecciated.
Thanks in advance and have a great day,
A.
______________________________________
NLS_PARAMETERS
