Encyclopedia in the processing date oracle TO_DATE format Day: dd number 12 dy abbreviated fri day spelled out friday ddspth spelled out, ordinal twelfth Month: mm number 03 mon abbreviated mar month spelled out march Year: yy two digits 98 yyyy four digits 1998 24 hours Under the format: 0:00:00 - 23:59:59 .... 12 hours format The time range is: 1:00:00 - 12:59:59 ... 1. Date and character conversion Function usage (to_date, to_char) 2. Select to_char (to_date (222, 'j'), 'JSP') from DUAL Display Two Hundred TWENTY-TWO 3. Seeking a day Select to_Char (to_date ('2002-08 -26 ',' YYYY-MM-DD '),' Day ') from Dual; Monday Select TO_CHAR (TO_DATE (' 2002-08-26 ',' YYYY-MM-DD '),' Day ',' NLS_DATE_LANGUAGE = American ') from Dual; Monday Settings Date Language ALTER SESSION SET NLS_DATE_LTER SESSION SET NLS_DATE_LANGUAGE =' American '; can also be to_date (' 2002-08-26 ',' YYYY-MM-DD ',' NLS_DATE_LANGUAGUAGUAGUAGUAGAN ') 4. Two Days of the day Select Floor (Sysdate - TO) Date ('20020405', 'YYYYMMDD')) from Dual; 5. Time is NULL Usage Select ID, Active_Date from Table1 Union Select 1, TO_DATE (NULL) from DUAL; Note To use to_date (null) 6. a_date between to_date ('20011201', 'YYYYMMDD') and to_date ('2001231', 'YYYYMMDD') Then 12:00 on December 31 and 12:00 on December 1st is not included in this range.
So, when time needs to be exactly, try to_char is still necessary 7. Date format conflict issues Enter the format to see the type of Oracle character set you installed, such as US7ASCII, Date format is: '01 -jan-01 'alter system set NLS_DATE_LANGUAGE = American alter session set NLS_DATE_LANGUAGE = American to_date or write in the select to_char (to_date (' 2002-08-26 ',' yyyy-mm-dd '),' day ',' NLS_DATE_LANGUAGE = American ') from dual; pay attention to me this is just a NLS_DATE_LANGUAGE, of course, there are many, you can see 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 ( '2002-02-28', 'YYYY-MM-DD') - TO_DATE ('2002- 02-01', 'YYYY-MM-DD') 1) WHERE TO_CHAR (To_Date ('2002-02-01' , 'YYYY-MM-DD') RNUM-1, 'D') Not in ('1', '7') Find 2002-02-28 to 2002-02-01 Said Monday and Seven days DBMS_UTILITY.GET_TIME is called before and after, it will subtilize the result (get 1/100 seconds, not 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, FORMAT CODE DAY MON-Sun, For Format Code DY 1-7, 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 of the first record is the same as the last line, the same function can create a function to handle this problem Create or Replace function sys_date return date is beginning, qRN sysdate; end; select to_char (sys_date, 'hh: mi: ss') from all_Objects; 12. Get a small number of SELECT EXTRACT (HOUR from timestamp '2001-02-16 2:40') from offer sql> select sysdate, to_char (sysdate, 'hh') from dual; sysdate to_char (sysdate, 'hh' ) ----------------------------------------- 2003-10-13 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 Get the Year of the Year and this similar 13.-year-month SELECT OLDER_DATE, NEWER_DATE , Years, Months, ABS (Trunc (NEWER_DATE- Add_MONTHS (Older_Date, Years * 12 Months))))))))) "" SELECT TRUNC (NEWER_DATEEEN (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. Processing February days indefinite way select to_char (add_months (last_day (sysdate ) 1, -2), 'YYYYMMDD'), Last_Day (sysdate) from Dual 16. Find this year SELECT Add_MontHS (Trunc (Sysdate, 'Year'), 12) - Trunc (Sysdate, 'Year') from Dual leap year treatment method to_char (to_day (to_date ('02 ')),' dd ') If it is 28, it is not a leap year 17. The difference between YYYY and RRRR'
