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

Oracle-20SEP-date format conversion to 9-20 and compare size 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 the size of www.2cto.com action layer String endDate mapper layer (db t_o_d.tk_ftda = 01sep, varchar2) <if test = "endDate! = Null "> and TO_CHAR (TO_DATE (t_o_d.tk_ftda, 'ddmon', 'nls _ date_language = American '), 'Mm-dd') & lt ;#{ endDate, jdbcType = VARCHAR} </if> Appendix: oracle to_char () query the data between two years, months, or dates by comparing the date ranges -- query the 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. r Eceivedate, 'yyyy-mm') <= '1970-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 ') <= '1970-01-01 'www.2cto.com oracle comparison of the time when the to_date conversion time of the character string in oracle without time and minute is equivalent to the date field with time and minute (>=, <=, =), because the string does not contain time and minute seconds, the default value of time and second after conversion is 00:00:00. in oracle, date is compared by a time point, accurate to seconds or even milliseconds in milliseconds, so the time and second of the date field does not match the date after conversion without the time and second string. Example: Table huhai_seq_value all data MYDATE-----------2009-03-022009-03-022009-03-022009-03-032009-03-032009-03-03 -- sql1: select hsv. mydate from huhai_seq_value hsv where hsv. mydate = to_date ('1970-03-03 ', 'yyyy-mm-dd'); sql1 Query Result: www.2cto.com MYDATE-----------2009-03-032009-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-----------2 032009-03-032009-03--03-03-03 conclusion: it is best to use the to_char function to convert the date without the time, minute, second, and then compare it with the string. Use between to compare two time cases 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: 07yyy three digits three-Year display value: 007 yyyy four digits four-digit annual display value: 2007 Month: mm number two-month display value: 11mon abbreviated character set display value: November, if the English version, show nov Month spelled out character set display value: November, if the English version, show November Day: dd number the displayed value for the Day of the month: 02ddd number display value for the day of the current year: 02dy abbreviated display value for the day of the week: Friday, if the English version, show Friday spelled out full write value for the day of the week: Friday, if the English version, display Friday ddspth spelled out, ordinal twelfth Hour: hh two digits 12 hours, the displayed value is: 01hh24 two digits 24 hours, and the displayed value is 13 Minute: mi two digits 60 hexadecimal display value: 45 Second: ss two digits 60 hexadecimal display value: 25 other Q digit quarterly display value: 4WW digit Display value in the week of the year: 44 W digit Display value for the week of the current month: the time range in the format of 1 24 hours is: 0:00:00-23:59:59 .... the time range in 12-hour format is: 1:00:00-1 2:59:59 .... 1. date and character conversion function usage (to_date, to_char) select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss') as nowTime from dual; // convert the date to the string select to_char (sysdate, 'yyyy') as nowYear from dual; // obtain the year's select to_char (sysdate, 'mm') as nowMonth from dual; // obtain the monthly select to_char (sysdate, 'dd') as nowDay from dual; // obtain the date of the date select to_char (sysdate, 'hh24') as nowHour from dual; // select to_char (sysdate, 'mi') a when obtaining the time S nowMinute from dual; // obtain the time score of select to_char (sysdate, 'ss') as nowSecond from dual; // obtain the second select to_date ('2017-05-07 13:23:44 ', 'yyyy-mm-dd hh24: mi: ss') from dual // 2. select to_char (to_date (222, 'J'), 'jsp ') from dual display Two Hundred Twenty-Two 3. calculate the day of the week as select to_char (to_date ('1970-08-26 ', 'yyyy-mm-dd'), 'day') from dual; monday select to_char (to_date ('1970-08-26 ', 'yyyy-mm-dd'), 'day', 'nls _ DATE_LANGUA GE = American ') from dual; Monday sets the date language alter session set NLS_DATE_LANGUAGE = 'American'; or TO_DATE ('2017-08-26 ', 'yyyy-mm-dd ', 'nls _ DATE_LANGUAGE = American ') 4. select floor (sysdate-to_date ('201312', 'yyyymmdd') from dual; 5. if the time is null, select id, active_date from table1 UNION select 1, TO_DATE (null) from dual; note that TO_DATE (null) 6 is used. month difference a_date between to_date ('20140901', 'yyyymmdd') And to_date ('123', 'yyyymmdd') is not included in this range after on January 1, 20011231 and before on January 1. So when the time needs to be accurate, I think to_char is still necessary 7. for date format conflict, the input format depends on the type of the ORACLE character set you have installed, for example, US7ASCII. The date format is: '01-Jan-01 'alter system set NLS_DATE_LANGUAGE = American alter session set NLS_DATE_LANGUAGE = American or write select to_char (to_date ('2017-08-26 ', 'yyyy-mm-dd'), 'day', 'nls _ DATE_LANGUAGE = American ') from dual. Note that I only use NLS_DATE_LANGUAGE. Of course there are many more, you can view select * from nls_session_parameters select * from V $ NLS_PARAMETERS 8. select count (*) from (select rownum-1 rnum from all_objects where rownum <= to_date ('2017-02-28 ', 'yyyy-mm-dd ') -to_date ('1970-02-01 ', 'yyyy-mm-dd') + 1) where to_char (to_date ('1970-02-01', 'yyyy-mm-dd ') + rnum-1, 'd) not in ('1', '7') Find the days between and except Monday and seven respectively call DBMS_UTILITY.GET_TIME, subtract the result (1/100 seconds instead of milliseconds ). 9. select months_between (to_date ('01-31-1999 ', 'Mm-DD-YYYY '), to_date ('12-31-1998', 'Mm-DD-YYYY ') "MONTHS" from dual; 1 select months_between (to_date ('02-01-1999 ', 'Mm-DD-YYYY '), to_date ('12-31-1998', 'Mm-DD-YYYY ') "MONTHS" from dual; 1.03225806451613 10. next_day usage Next_day (date, day) Monday-Sunday, for format code DAY Mon-Sun, for format code DY 1-7, for format code D 11 select to_char (sysdate, 'hh: mi: ss') TIME from all_objects note: the TIME and The next row is the same. You can create a function to solve this problem. create or replace function sys_date return date is begin return sysdate; end; select to_char (sys_date, 'hh: mi: ss ') from all_objects; 12. obtain the number of hours extract () to locate the field value of the date or interval value select extract (hour from timestamp '2017-02-16 2:38:40 ') from offer SQL> select sysdate, to_char (sysdate, 'hh ') from dual; SYSDATE TO_CHAR (SYSDATE, 'hh') ------------------ ----------------------- 19:35:21 07 SQL> select sysdate, to_char (sysdate, 'hh24') from dual; SYSDATE TO_CHAR (SYSDATE, 'hh24') -------------------- ----------------------- 2003-10-13 19:35:21 19 13. select older_date, newer_date, years, months, abs (trunc (newer_date-hour (older_date, years * 12 + months) days from (select trunc (hour (newer_date, older_date)/12) YEARS, mod (trunc (months_between (newer_date, Older_date), 12) MONTHS, newer_date, older_date from (select hiredate older_date, add_months (hiredate, rownum) + rownum newer_date from emp) 14. if the number of days in a month is not fixed, select to_char (add_months (last_day (sysdate) + 1,-2), 'yyyymmdd'), last_day (sysdate) from dual 16. find the number of days of this year select add_months (trunc (sysdate, 'Year'), 12)-trunc (sysdate, 'Year ') from dual leap method to_char (last_day (to_date ('02' |: year, 'mmyyyy' ), 'Dd') if it is 28, it is not a leap year 17. difference between yyyy and rrrr 'yyyy99 TO_C ------- ---- yyyy 99 0099 rrrr 99 1999 yyyy 01 0001 rrrr 01 2001 18. select to_char (NEW_TIME (sysdate, 'gmt', 'est '), 'dd/mm/yyyy hh: mi: ss'), sysdate from dual; 19.5 seconds one interval Select TO_DATE (FLOOR (TO_CHAR (sysdate, 'ssss')/300) * 300, 'ssss'), TO_CHAR (sysdate, 'ssss ') from dual 2002-11-1 9:55:00 35786 SSSSS indicates 5 digits in seconds 20. select TO_CHAR (SYSDAT E, 'ddd '), sysdate from dual 310 10:03:51 21. computing hour, minute, second, millisecond select Days, A, TRUNC (A * 24) Hours, TRUNC (A * 24*60-60 * TRUNC (A * 24) Minutes, TRUNC (A * 24*60*60-60 * TRUNC (A * 24*60) Seconds, TRUNC (A * 24*60*60*100-100 * TRUNC (A * 24*60*60) mSeconds from (select trunc (sysdate) Days, sysdate-trunc (sysdate) A from dual) select * from tabname order by decode (mode, 'fifo ', 1,-1) * to_char (rq, 'yyyymmddhh24mis S '); // floor (date2-date1)/365) as year floor (date2-date1, 365)/30) as month d (mod (date2-date1, 365), 30) as a day. 23. the next_day function returns the date of the next week. day is 1-7 or Sunday-Saturday. 1 indicates that Sunday next_day (sysdate, 6) is the next Friday starting from the current day. The following number is counted from Sunday. 1 2 3 4 5 6 7 1234 5 6 --------------------------------------------------------------- select (sysdate-to_date ('2017-12-03 12:55:45 ', 'yyyy-mm-dd hh24: mi: ss ')) * 24*60*60 from ddual date returns the day and then converts it to ss 24, round [round to the nearest date] (day: round to the nearest Sunday) select sysdate S1, round (sysdate) S2, round (sysdate, 'Year') year, round (sysdate, 'month') month, round (sysdate, 'day ') DAY from dual 25, trunc [truncates to the closest date, in days], returns the date type select sysdate S1, trunc (sysdate) S2, // returns the current date, trunc (sysdate, 'Year') year without hour, // returns the month of the current YEAR. If the hour is not hour, trunc (sysdate, 'month') MONTH, // returns the 1 day of the current month, with no hour, minute, second, trunc (sysdate, 'day') DAY // returns the Sunday of the current week, with no hour, minute, second from dual 26, return the latest date in the date list, select greatest ('01-January-04 ', '04-January-04', '10-February-04 ') from dual 27. calculation time difference Note: oracle time difference is in the unit of days, so converted to the Year, the select floor (to_number (sysdate-to_date ('2017-11-02 15:55:03 ', 'yyyy-mm-dd hh24: mi: ss')/365) as spanYears from dual // Time Difference-year select ceil (moths_between (sysdate-to_date ('2017-11-02 15:55:03 ', 'yyyy-mm-dd hh24: mi: ss '))) as spanMonths from dual // Time Difference-monthly select floor (to_number (sysdate-to_date ('2017-11-02 15:55:03 ', 'yyyy-mm-dd hh24: mi: ss '))) as spanDays from dual // Time Difference-day select floor (to_number (sysdate-to_date ('2017-11-02 15:55:03 ', 'yyyy-mm-dd hh24: mi: ss ')) * 24) as spanHours from dual // Time Difference-When select floor (to_number (sysdate-to_date ('2017-11-02 15:55:03 ', 'yyyy-mm-dd hh24: mi: ss ') * 24*60) as spanMinutes from dual // Time Difference-minute select floor (to_number (sysdate-to_date ('2017-11-02 15:55:03 ', 'yyyy-mm-dd hh24: mi: ss') * 24*60*60) as spanSeconds from dual // Time Difference-second 28. update Time Note: oracle time addition and subtraction is based on the number of days, set the change volume to n, so it is converted to the year, the day select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss '), to_char (sysdate + n * 365, 'yyyy-mm-dd hh24: mi: ss') as newTime from dual // change time-year select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss'), add_months (sysdate, n) as newTime from dual // change time-month select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss'), to_char (sysdate + n, 'yyyy-mm-dd hh24: mi: ss ') as newTime from dual // change time-day select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss'), to_char (sysdate + n/24, 'yyyy-mm-dd hh24: mi: ss') as newTime from dual // select to_char (sysdate, 'yyyy-mm-dd hh24: mi when changing the time: ss '), to_char (sysdate + n/24/60, 'yyyy-mm-dd hh24: mi: ss') as newTime from dual // change time-minute select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss'), to_char (sysdate + n/24/60, 'yyyy-mm-dd hh24: mi: ss ') as newTime from dual // change time-seconds 29. search for the first day of the MONTH, SELECT Trunc (SYSDATE, 'month')-1, 'month') First_Day_Last_Month, Trunc (SYSDATE, 'month')-1/86400 Last_Day_Last_Month, trunc (SYSDATE, 'month') First_Day_Cur_Month, LAST_DAY (Trunc (SYSDATE, 'month') + 1-1/86400 Last_Day_Cur_MonthFROM dual;