When the to_date conversion time of a string without the time and minute is compared with the date field with the time and second (gt;, lt;,), because the string does not contain the time
When the to_date conversion time of a string without the time and second is compared with the date field with the time and second (gt ;=, lt ;=, =, because the string does not contain Time Division
Date Format Conversion:
SQL> select to_char (to_date ('01sep', 'ddmon', 'nls _ date_language = American '), 'Mm-dd') from dual;
TO_CHAR (TO_DATE ('01sep', 'ddmon
------------------------------
09-01
Compare size
Action layer String endDate
Mapper layer (t_o_d.tk_ftda = 01sep, varchar2 in db)
And TO_CHAR (TO_DATE (t_o_d.tk_ftda, 'ddmon', 'nls _ date_language = American '), 'Mm-dd') <=# {endDate, jdbcType = VARCHAR}
Appendix:
Comparison of date ranges using to_char () in Oracle
Query data between two years, months, or dates
-- Query year
Select * from document_main t where to_char (t. receivedate, 'yyyy')> = '000000' and to_char (t. receivedate, 'yyyy') <= '000000'
-- Query the month
Select * from document_main t where to_char (t. receivedate, 'yyyy-mm')> = '2017-01' and to_char (t. receivedate, 'yyyy-mm') <= '2017-01'
-- Query date
Select * from document_main t where to_char (t. receivedate, 'yyyy-mm-dd')> = '2017-01-01 'and to_char (t. receivedate, 'yyyy-mm-dd') <= '2017-01-01'
Oracle time comparison
When the to_date conversion time of a string without time and minute is compared with the date field with time and minute seconds (>=,<=, =), because the string does not contain time and minute seconds, therefore, after the conversion, the default value is 00:00:00. in oracle, the comparison of date is based on a time point, accurate to seconds or even milliseconds in microseconds, therefore, if the time and second values of the date field are not 00:00:00, they do not match the date value after String Conversion without the time and second values. Example:
All huhai_seq_value data in the table
MYDATE
-----------
2009-03-
2009-03-02
2009-03-02
2009-03-03
2009-03-03
2009-03-03
-- Sql1:
Select hsv. mydate from huhai_seq_value hsv
Where hsv. mydate = to_date ('2017-03-03 ', 'yyyy-mm-dd ');
Sql1 query results:
MYDATE
-----------
2009-03-03
2009-03-03
-- Sql2:
Select hsv. mydate from huhai_seq_value hsv
Where to_char (hsv. mydate, 'yyyy-mm-dd') = '2017-03-03 ';
SQL query result:
MYDATE
-----------
2009-03-03
2009-03-
2009-03-03
Conclusion: We recommend that you use the to_char function to convert the date value without time, minute, and second, and then compare it with the string.
Use between to compare two times
Example
Select * from tbl_name where issue_time between to_date ('2017-04-12 23:59:59 ', 'yyyy-MM-DD HH24: MI: ss') and sysdate;
TO_DATE format (take time: 13:45:25 as an example)
Year:
Yy two digits two-year display value: 07
Yyy three digits: 007
Yyyy four digits four-digit year display value: 2007
Month:
Mm number: 11
Mon abbreviated Character Set indicates the displayed value: January 1, November. If the English version is used, nov is displayed.
Month spelled out Character Set indicates the displayed value: January 1, November. If the English version is used, November is displayed.
Day:
Dd number: 02
Ddd number: 02
Short display value of dy abbreviated for the day of the week: Friday. If it is an English version, fri is displayed.
Day spelled out full display value for the day of the week: Friday. If the English version is used, the display is Friday.
Ddspth spelled out, ordinal twelfth
Hour:
Hh two digits 12 hour display value: 01
Hh24 two digits 24 hours: 13
Minute:
Mi two digits 60 hexadecimal value: 45
Second:
Ss two digits 60 hexadecimal value: 25
,