Oracle to_date error caused by different language Environments

Oracle to_date error caused by different language Environments

I wrote a stored procedure and used a function in it. The function contains a to_date (dateFrom, 'yyyy/mm/dd'). After running the function, I found that the data was inserted incorrectly, the inserted data is "0001/9/14 ". I don't know why the data is like this. After debugging, I found that the date "" entered in the test window changed to "01-SEP-14" in the program, it turned out to be September 14.

If you know the reason, let's talk about the solution: to_date (to_char (dateFrom, 'yyyy/mm/dd'), 'yyyy/mm/dd '). Run again and the record is successful.

Oracle insertion date Error

The environment variable nls_date_language is not set.

The following is the query result when no settings are set:
SQL> select to_char (sysdate, 'dd-mon-yy') from dual;

TO_CHAR (SYSDAT
--------------
5-10-11 months

Query the set statement
SQL> show parameter nls_date_language;

NAME TYPE VALUE
------------------------------------------------------
Nls_date_language string

Modify the set statement
SQL> alter session set nls_date_language = 'American ';

The session has been changed.

Query after modification
SQL> select to_char (sysdate, 'dd-mon-yy') from dual;

TO_CHAR (SYSD
------------
05---11

SQL> SELECT TO_DATE ('05-oct-11 ', 'dd-mon-yy') from dual;

TO_DATE ('05-
------------
05---11

ORACLE to_date

It must be because the format of a record does not conform to YYYYMMDD.
You should write a function to find out and correct such exceptions.
Create function is_valid_date (p_yyyyymmdd in varchar2) return number is
Rochelle date;
Begin
Rochelle date = to_date (p_yyyyymmdd, 'yyyymmdd ');
Return 1;
Exception when others then
Return 0;
End;

Then write the select statement.
Select * from pc_xx where is_valid_date (substr (rydjsj, 1, 8) = 0;