Oracle-20SEP-date format to 9-20 and compare size

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

,