TO_DATE format and other date functions in Oracle

In Oracle, The TO_DATE format and other date functions are in the TO_DATE format (take the time: 13:45:25 as an example) Year: yy two digits two-Year display value: 07 yyy three digits three-Year display value: 007 yyyy four digits four-digit year display value: 2007 Month: mm number two-digit Month display value: 11 mon abbreviated character set display value: November, if it is English version, show nov month spelled out character set show value: November, if the English version, show November Day: dd number the Day of the month show value: 02 ddd number the Day of the year show value: 02 short display value of dy abbreviated for the day of the week: Friday. If the English version is used, display the full display value of fri day spelled out for the day of the week: Friday. If the English version is used Version, display Friday ddspth spelled out, ordinal twelfth Hour: hh two digits 12 small value: 01 hh24 two digits 24 small value: 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: 4 WW 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 the 12-hour format is: 1:00:00-12:59:59 .... 1. date and character conversion function usage (to_date, to_char) select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss') as n OwTime from dual; // The date is converted 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') as nowMinute from dual when obtaining the time; // obtain the time score of select to_char (sysdate, 'ss ') as nowSecond from dual; // get the second sel of the time Ect 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_LANGUAGE = American') from dual; monday sets the date language alter session set NLS_DATE_LANGUAGE = 'American '; you can also _ DATE ('1970-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 ('20140901', 'yyyymmdd ') therefore, it is not included in this range after on January 1, December 31 and before on January 1, December 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 ') ------------------ ----------------------- 1 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 (SYSDA TE, '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, 'yyyymmddhh24mi Ss'); // 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_Month FROM dual;