What is not found in query data during splicing time during Oracle storage?

Two time parameters are passed in when a stored procedure is used to query a project. The query content is like this.

  v_sql1:=v_sql1||' and  logtime between '''||t_starttime||''' and '''||t_endtime||'''  ;

T_starttime and t_endtime are input parameters.

When performing a query, the number executed and the total number are always not correct. The total number queried is 820, but the number that can be queried in the database is 830. The difference is 10. However, in PLSQL, the statistical function is called to test, but the result is 830, which is the same as the default one. Therefore, a problem occurs when calling the statistical function parameters in the stored procedure.

In PLSQL, we tracked the String concatenation In the stored procedure and finally located the time above. That is, the class is the code above.

Assume that the input parameters January 00:00:01 and January 23:59:59 are converted to the last strings '01-March 13-13 'and the time minutes and seconds after '10-March 13-13' are lost, this string conversion method is set by oralce. In normal queries, there is no problem. However, in the stored procedure, String concatenation is required. After the time, minute, and second are omitted, the concatenated string is like this.

V_sql1: = v_sql1 | 'and logtime between' 01-March 13-13' and '10-March 13-13 ';

But what we want is

v_sql1:=v_sql1||' and  logtime between ’2013-01-01 00:00:01’ and ’2013-01-10 00:00:01’;

Of course, the format above is incorrect. It is inconsistent with the default character format in Oracle. If you execute SQL statements, a string format error is reported.

Because between is included on both sides during direct conversion, the data we last queried is 00:00:00 to 00:00:00.

This is a day's difference from the query data we originally expected. Therefore, you must pay attention to the format when splicing time, and the accuracy is lost during conversion.

So how can we solve the problem caused by this accuracy error? In fact, we only need to replace the variable here to solve it.

String

v_sql1:=v_sql1||' and  zcsj between :1 and :2 ';

During execution

  Execute Immediate v_sql1 using t_start,t_end;