Data loss during oracle Storage Using date, minute, and second, oracledate

Data loss during oracle Storage Using date, minute, and second, oracledate

Today, a developer called me and said that there was a strange phenomenon. In the stored procedure, values of the date type are lost in minutes, minutes, And seconds. Here is an experiment:

SQL> drop table test purge;

SQL> create table test
(
Fill_date date
);
SQL> insert into test values (sysdate );
SQL> commit;
SQL> select to_char (fill_date, 'yyyy-MM-dd HH24: mi: ss') from test;
TO_CHAR (FILL_DATE ,'
-------------------

17:47:22

SQL> CREATE OR REPLACE PROCEDURE test_p
Is
S_ SQL varchar2 (500 );
S_date date;
Begin
S_date: = to_date ('2017-07-18 17:24:32 ', 'yyyy-MM-dd HH24: mi: ss ');
S_ SQL: = 'Update test SET fill_date = ''' | s_date | '''';
Execute immediate s_ SQL;
Commit;
End;

/

SQL> call test_p ();

SQL> select to_char (fill_date, 'yyyy-MM-dd HH24: mi: ss') from test;
TO_CHAR (FILL_DATE ,'
-------------------
00:00:00-- We can see that the time, minute, and second have been lost. It is suspected that implicit conversion causes

-- Adjust the stored procedure to see what is printed.

SQL> CREATE OR REPLACE PROCEDURE test_p
Is
S_ SQL varchar2 (500 );
S_date date;
Begin
S_date: = to_date ('2017-07-18 17:24:32 ', 'yyyy-MM-dd HH24: mi: ss ');
S_ SQL: = 'Update test SET fill_date = ''' | s_date | '''';
Dbms_output.put_line (s_ SQL );
Execute immediate s_ SQL;
Commit;
End;

/

SQL> set serveroutput on

SQL> call test_p ();
UPDATE test SET fill_date = '18-August 14-14'
The call is complete.

-- If implicit conversion is performed, bind the variable.

SQL> CREATE OR REPLACE PROCEDURE test_p
Is
S_ SQL varchar2 (500 );
S_date date;
Begin
S_date: = to_date ('2017-07-18 17:24:32 ', 'yyyy-MM-dd HH24: mi: ss ');
S_ SQL: = 'Update test SET fill_date =: 1 ';
Execute immediate s_ SQL using s_date;
Commit;
End;

/

SQL> call test_p ();
The call is complete.

SQL> select to_char (fill_date, 'yyyy-MM-dd HH24: mi: ss') from test;
TO_CHAR (FILL_DATE ,'
-------------------
17:24:32

Oracle Stored Procedure string converted to date

Select to_date (to_char (firstTiem, 'yyyy-MM-dd hh24: mi: ss'), 'yyyy-MM-dd hh24: mi: ss') into fTime from dual;
If firstTiem is of the varchar2 type.

How to call the date type in oracle stored procedures

It is not called. If to_date ('20140901', 'yyyymmdd') is required to be converted to Date