Summary of oracle Database date statements

Oracle databases perform detailed operations on date data. The expression of date data involves the system time zone and time format. Oracle system functions such as to_date ('string', 'format') and to_char (Date, 'format') are often used. Therefore, operations on data of the date type are a bit complicated. When operating on oracle Data of the date type, pay attention to a lot of things. 1. sysdate indicates the current date, and oracle database indicates the object of the current date. It is often used to obtain the current date: select sysdate from dual; 2. the to_date ('string', 'format') function converts a String to a date object in the format. This function is very powerful. The String can contain delimiters, which can be spaces,/, \, and other symbols except numbers and letters. However, the date and time must have the same separator. The two can be different. Format can be in many formats, but it must contain the Strring string format. Otherwise, to_date cannot be converted to the date format because the format is missing. To_date format (take 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 annual display value: 2007 Month: mm number two-month display value: 11 mon abbreviated [small; short] character set display value: January 1, November. If the English version is used, nov Month spelled out character set display value: on April 9, November, if the English version is used, the displayed value of November Day: dd number in the current month is: 02 ddd number in the current year. displayed value: 02 select to_date (sysdate, 'dy ') from dual, select to_date ('2014 08:13:43 ', 'yy-MM-dd HH: mm: ss') from dual dy abbreviated the day of the week abbreviated to display value: Friday, if the English version, show fri day spelled out full write display value for the day of the week: Friday, if the English version, display Friday ddspth spelled out, ordinal twelfth Hour: hh two digits 12 small display value: 01 hh24 two digits 24 hour display value: 13 Minute: mi two digits 60 hexadecimal display value: 45 Second: ss two digits 60 hexadecimal display value: 25 other Q digit Display values for the quarter: 4 WW digit Display value for the week of the current year: 44 W digit Display value for the week of the current month: 1 24 hours format time range: 0:00:00-23:59:59 .... the time range in the 12-hour format is: 1:00:00-12:59:59 .... 1. obtain the year, month, day, hour, minute, second of the current date select to_char (sysdate, 'yyyy') as nowYear from dual; // obtain the year of the date select to_char (sysdate, 'mm') as nowMonth from dual; // select to_char (sysdate, 'dd') as nowDay from dual; // 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 from select to_char (sysdate, 'ss') as nowSecond from dual; // get the second of the time 2. calculate the day of the week as select to_char (sysdate, 'dy ') from dual; select to_char (to_date ('20170101', 'yyyy/mm/dd'), 'day ') from dual3. days between two days (oracle Database minus two days to get is days) select floor (sysdate-to_date ('20170101', 'yyyy-mm-dd') from dual; select to_char (sysdate-to_date ('20140901', 'yyyy-mm-dd') from dual; // return number of days with decimal point 4. select floor (months_between (sysdate, to_date ('000000', 'yyyy-mm-dd') from dual; 5. view the number of days of this year in select add_months (trunc (sysdate, 'Year'), 12)-trunc (sysdate, 'Year ') from dual6. set the date language alter session set nls_date_language = 'simple chinese '; alter session set nls_date_language = 'American'; 7. next_day (date, day) usage date indicates a date, and day indicates Sunday to next Saturday, that is, 1-7.select next_day (sysdate, 7) from dual; // select next_day (sysdate, 1) from dual; // next Sunday 8. difference between yyyy and rrrr 'yyyy99 TO_C ------- ---- yyyy 99 0099 rrrr 99 1999 yyyy 01 0001 rrrr 01 20019. select to_char (NEW_TIME (sysdate, 'gmt', 'est '), 'dd/mm/yyyy hh: mi: ss'), sysdate from dual; AST Atlantic standard ADT Atlantic when BST white standard BDT white order when CST Central Standard when CDT central when EST East standard when EDT east when GMT Greenwich Mean Time HST Alaska Hawaii Standard Time HDT Alaska Hawaii when MST Mountain Standard mountain when NST new Finland standard PST Pacific Standard PDT pacific when YST YuKon standard when YDT YuKon then 10.5 seconds interval Select TO_DATE (FLOOR (TO_CHAR (sysdate, 'sssss')/300) * 300, 'ssss'), TO_CHAR (sysdate, 'ssss') from dual11. find the first day of the month, the last day 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;
Author nicegege