Oracle Stored Procedure get time format for YYYY-MM-DD

Environment: Oracle10g, 11g problem reproduction: In PLSQL command window, it is found that the time format obtained by the stored procedure does not meet the expected requirements. SQLgt; selectsysdatefro

Environment: Oracle 10g, 11g problem reproduction: in the Command window of PL/SQL, it is found that the time format obtained by the stored procedure does not meet the expected requirements. SQLgt; select sysdate fro

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.

This article permanently updates the link address: