Oracle stored procedure gets the YYYY-MM-DD time format

Environment: Oracle 10g,11g

Problem recurrence: Under the Command window in PL/SQL, the time format found by the stored procedure does not meet the expected requirements.

Sql> SelectSysdate fromdual; Sysdate----------- the- A- -Executedinch 0Secondssql> SetServeroutput onSQL> Declare2pro_date date;3 begin4 SelectSysdate intoPro_date fromdual;5Dbms_output.put_line (pro_date);6 End;7 / --December- -PL/Sqlproceduresuccessfully completedexecutedinch 0.016Seconds

Process Method 1: Convert the result to a string format:

Sql>SQL> Declare2pro_date date;3 begin4 SelectSysdate intoPro_date fromdual;5Dbms_output.put_line (To_char (Pro_date,'YYYY-MM-DD'));6 End;7 / the- A- -PL/Sqlproceduresuccessfully completedexecutedinch 0.016Secondssql>SQL> Declare2pro_date date;3 begin4 SelectSysdate intoPro_date fromdual;5Dbms_output.put_line (pro_date);6 End;7 / --December- -PL/Sqlproceduresuccessfully completedexecutedinch 0Seconds

Treatment Method 2: Change the Nls_date_format of the session

Sql> AlterSessionSetNls_date_format='YYYY-MM-DD Hh24:mi:ss'; Session alteredexecutedinch 0.015Secondssql>SQL> Declare2pro_date date;3 begin4 SelectSysdate intoPro_date fromdual;5Dbms_output.put_line (pro_date);6 End;7 / the- A- -  One: -: thePL/Sqlproceduresuccessfully completedexecutedinch 0Secondssql> AlterSessionSetNls_date_format='YYYY-MM-DD'; Session alteredexecutedinch 0Secondssql>SQL> Declare2pro_date date;3 begin4 SelectSysdate intoPro_date fromdual;5Dbms_output.put_line (pro_date);6 End;7 / the- A- -PL/Sqlproceduresuccessfully completedexecutedinch 0Seconds

Summary: in Oracle stored procedures want to get YYYY-MM-DD time format, can be converted to string processing, you can temporarily specify the Nls_date_format variable of the session, you can also modify the client's environment variables as a whole.

Oracle stored procedure gets the YYYY-MM-DD time format