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 time in 199,824-hour format ranges: 0:00:00 - 23:59:59 .... 12 hours of 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 = 'American'; can also be to_date ('2002-08-26', 'YYYY-MM-DD', 'NLS_DATE_LANGUAGUE = American') 4. The number of days during the two days Select SELECT Floor (sysdate - to_date ('20020405')) 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') So December 31 noon 1 It is not included in this area before 2 o'clock and December 1st.
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-yyyyy ')) "MONTHS" from dual; 1.03225806451613 1 0. 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 first record of the Time is the same as the last line to create a function to handle this problem Create or replace function sys_date return date is beg Return sysdate; end; select to_char (sys_date, 'hh: mi: ss' "from all_objects; 12. Get a small number Select Extract (HOUR from timestamp '2001-02-16 2:38: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. The Handling of the Year SELECT OLDER_DATE, NEWER_DATE, YEARS, MONTHS, ABS (Trunc (NEWER_DATE- Add_Months (Older_Date, Years * 12 Months)))) " (months_between (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. Handling the number of monthly days 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 (Last_Day (to_date ('02' ||: Year, 'mmyyyy')), 'DD') If it is 28 Leap year 17. The difference between YYYY and RRRR 'YYYY9 9 TO_C ------- ---- YYYY 99 0099 RRRR 99 1999 YYYY 01 0001 RRR 01 2001 18. Processing of different time zones Select to_CHAR (New_Time (sysdate, 'gmt', 'Est'), 'DD / MM / YYYY HH: MI: SS '), sysdate from dual; 19. 5 seconds a spacing select to_date (floor (to_char (sysdate,' sssss') / 300) * 300, 'sssss', to_char (sysdate, 'Sssss') from Dual 2002-11-1 9:55:00 35786 SSSSS represents 5-digit seconds 20. Select to_char (sysdate, 'DDD'
