Oracle PL/SQL uncertain prompt (nls_date_format)

I just tested a program and reported an error saying that the data was not found:

Rochelle date1! = L_date2
L_date1 = 26-May-11, l_date2 = 26-May-11

......

It turns out that it was nls_date_format.

Oracle uses built-in programs such as dbms_output.put_line or fnd_file.put_line to output date-type parameters, the date format defined by nls_date_format is automatically applied, happens to the current database, the date format defined by nls_date_format is DD-MON-RR, no time, SEC, however, the two dates involved in the comparison include time, minute, and second, and the difference lies in the time, minute, and second:

Declare <br/> l_date1 Date: = to_date ('2014/1/26', 'yyyy/MM/dd'); <br/> l_date2 Date: = to_date ('2014/1/26 16:58:00 ', 'yyyy/MM/DD hh24: MI: ss'); <br/> l_val nls_session_parameters.value % type; <br/> begin <br/> select value <br/> into l_val <br/> from nls_session_parameters <br/> where parameter = upper ('nls _ date_format '); <br/> dbms_output.put_line ('original: nls_date_format = '| l_val); </P> <p> dBm S_output.put_line ('= test if two dates equal with confused info = '); <br/> execute immediate 'alter session set nls_date_format = ''dd-MON-RR '''; <br/> If (l_date1! = L_date2) <br/> then <br/> dbms_output.put_line ('L _ date1! = L_date2 '); <br/> dbms_output.put_line ('L _ date1 =' | l_date1 | ', l_date2 =' | l_date2); <br/> end if; </P> <p> dbms_output.put_line ('= test if two dates equal with clear info = '); <br/> execute immediate 'alter session set nls_date_format = ''dd-MON-YYYY hh24: MI: s'''; <br/> If (l_date1! = L_date2) <br/> then <br/> dbms_output.put_line ('L _ date1! = L_date2 '); <br/> dbms_output.put_line ('L _ date1 =' | l_date1 | ', l_date2 =' | l_date2); <br/> end if; </P> <p> -- revert <br/> execute immediate 'alter session set nls_date_format = ''DD-MON-RR '''; <br/> end; <br/>

 

Output:

Original: nls_date_format = DD-MON-RR <br/>== test if two dates equal with confused info ===< br/> l_date1! = L_date2 <br/> l_date1 = 26-May-11, l_date2 = 26-May-11 <br/>== test if two dates equal with clear info ==< br/> l_date1! = L_date2 <br/> l_date1 = 26-may-2011 00:00:00, l_date2 = 26-may-2011 16:58:00 <br/>