In Oracle, to_char and to_date

To_char converts a date or number to a string to_date. in the database, the date type conversion function to_number converts the character to a number to_char. The to_char function is used to process the number to_char (number, 'format') to_char (salary, '$99,999.99'); Use the to_char function to process the date to_char (date, 'format '); to_number use the to_number function to convert the character to the number to_number (char [, 'format']) to_date use the to_date function to convert the character to the date to_date (char [, 'format']) number Format 9 represents a number 0 forcibly display 0 $ place a $ character l place a floating local currency character. display decimal point, display the date format of the thousands of indicators control description yyyy, YYY, YY represents four, three, two digits year Spelling mm digit month full spell mon month abbreviation dd digit day week full spell dy week abbreviation am represents morning or afternoon hh24, hh12 12 hour or 24 hour system mi minute SS second SP number spelling th number ordinal word "special character" suppose Special Character hh24: mi: SS am 15:43:20 PM date example: Select to_date ('2017-05-01 19:25:34 ', 'yyyy-MM-DD hh24: MI: ss ') from dualselect to_date ('2017-05-01 ', 'yyyy-MM-DD hh24: Mi') from dualselect to_date ('2017-05-01 19 ', 'yyyy-MM-DD hh24') from dualselect to_date ('2017-05-01 ', 'yyyy-m M-DD ') from dualselect to_date ('1970-05', 'yyyy-mm') from dualselect to_date ('20140901', 'yyyy') from dual date description: when the input parameters corresponding to HH, mi, and SS are omitted, Oracle uses 0 as the default value. If the input date data ignores the time part, Oracle sets the hour, minute, and second part to 0, that is, it will take the whole day. Similarly, if the DD parameter is ignored, Oracle uses 1 as the default value of the day, that is, it takes the entire month. However, do not be confused by this kind of "inertia". If the MM parameter is ignored, Oracle will not take the entire year and the entire month. Note: 1. when Oracle's to_date function is used for date conversion, the format of "yyyy-mm-dd hh: mm: SS" may be intuitively used for conversion, however, an error occurs in Oracle: "ora 01810 format code appears twice ". For example, select to_date ('1970-01-01 13:14:20 ', 'yyyy-mm-dd hh24: mm: ss') from dual, because SQL statements are case insensitive, mm and mm are considered to be the same format code, so Oracle SQL uses Mi instead of minutes. Select to_date ('1970-01-01 13:14:20 ', 'yyyy-mm-dd hh24: MI: ss') from dual; 2. in addition, it should be displayed in 24 hours to use hh24select to_char (sysdate, 'yyyy-mm-dd hh24: MI: ss') from dual; // Mi is minute select to_char (sysdate, 'yyyy-mm-dd hh24: mm: ss') from dual; // mm will display the format of month to_date (in time: 13:45:25) 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 Display value for two months: 11 mon abbreviated character set table Display value: January 1, November. If the English version is used, the Nov month spelled out character set is displayed. The displayed value is January 1, November. If the English version is used, the displayed value of November day: dd number is displayed: 02 DDD number display value for the day of the current year: 02 dy abbreviated display value for the day of the week: Friday, if the English version is used, display Fri day spelled out full write value for the day of the week: Friday, if the English version is used, display the Friday ddspth spelled out and ordinal twelfth hour: hh two digits 12 values: 01 hh24 two digits 24 values: 13 minute: mi Two digits 60 hexadecimal value: 45 Second: SS two digits 60 hexadecimal value: 25 other Q digit quarterly Value: 4 WW Digit Display value in the week of the current year: 44 W Digit Display value in the week of the current month: 1 24 hours format: 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 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 month's select to_char (sysdate, 'dd') as nowday from Du Al; // select to_char (sysdate, 'hh24') as nowhour from dual; // select to_char (sysdate, 'mi') when obtaining the time ') as 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 ('2017-08-26 ', 'yyy Y-mm-dd'), 'day') from dual; Monday select to_char (to_date ('2017-08-26 ', 'yyyy-mm-dd'), 'day ', 'nls _ date_language = 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; Be sure to use to_date (null) 6. 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 dual25, 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 time, minute, second from dual26, return the latest date in the date list select greatest ('01-January-04 ', '04-January-04', '10-February-04 ') from dual27. calculate the time difference. Note: oracle time difference is the number 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;