Oracle Stored Procedure get time format for YYYY-MM-DD
Environment: Oracle 10g, 11g
Problem reproduction: in the Command window in PL/SQL, it is found that the time format obtained by the stored procedure does not meet the expected requirements.
SQL> select sysdate from dual;
SYSDATE
-----------
2014-10-18
Executed in 0 seconds
SQL> set serveroutput on
SQL> declare
Pro_date date;
Begin
Select sysdate into pro_date from dual;
Dbms_output.put_line (pro_date );
End;
/
18-12-14
PL/SQL procedure successfully completed
Executed in 0.016 seconds
Solution 1: Convert the result to the string format:
SQL>
SQL> declare
Pro_date date;
Begin
Select sysdate into pro_date from dual;
Dbms_output.put_line (to_char (pro_date, 'yyyy-mm-dd '));
End;
/
2014-10-18
PL/SQL procedure successfully completed
Executed in 0.016 seconds
SQL>
SQL> declare
Pro_date date;
Begin
Select sysdate into pro_date from dual;
Dbms_output.put_line (pro_date );
End;
/
18-12-14
PL/SQL procedure successfully completed
Executed in 0 seconds
Solution 2: Change the NLS_DATE_FORMAT OF THE SESSION
SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24: mi: ss ';
Session altered
Executed in 0.015 seconds
SQL>
SQL> declare
Pro_date date;
Begin
Select sysdate into pro_date from dual;
Dbms_output.put_line (pro_date );
End;
/
11:18:15
PL/SQL procedure successfully completed
Executed in 0 seconds
SQL> alter session set nls_date_format = 'yyyy-mm-dd ';
Session altered
Executed in 0 seconds
SQL>
SQL> declare
Pro_date date;
Begin
Select sysdate into pro_date from dual;
Dbms_output.put_line (pro_date );
End;
/
2014-10-18
PL/SQL procedure successfully completed
Executed in 0 seconds
Summary: in Oracle stored procedures to get the YYYY-MM-DD's time format, you can convert to string processing, you can temporarily specify the session's NLS_DATE_FORMAT variable, you can also modify the client's overall environment variables.