Fw:oracle time operation (to be sorted)

To_date format (in time: 2007-11-02 13:45:25 for example) year:yy two digits two-bit year display value: 07yyy three digits three-bit year Display value: 007yyyy four digits four-digit year display value: 2007month:mm number two-bit month display value: 11mon abbreviate         The D character set represents the display value: November, in the English version, displays the spelled out character set of the month for the display value: November, if the English version, display November DAY:DD number    Day of the Month display value: 02DDD number the day of the year display value: 02dy abbreviated when in days abbreviated display value: Friday, if the English version, display Friday            Spelled out when in a few days full write display value: Friday, if the English version, display Friday ddspth spelled out, ordinal twelfth hour:hh 12-hour binary  Display value: 01hh24-digits 24-hour binary display value: 13minute:mi The digits 60 binary display value: 45SECOND:SS Digits 60 binary Display value: 25 Other Q digit Quarterly display value: 4WW digit The first few weeks of the year display values:      44W Digit Week of the month display values: 124-hour 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. Date and character conversion function usage (to_date, To_char) Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') as nowtime from dual;   Date converted to string select To_char (sysdate, ' yyyy ') as nowyear from dual;   Gets the year of the time select To_char (sysdate, ' mm ') as nowmonth from dual;   Gets the month of the time select To_char (sysdate, ' DD ') as nowday from dual;   Get time of day Select To_char (sysdate, ' hh24 ') as nowhour from dual;   Gets the time of the Select To_char (sysdate, ' mi ') as Nowminute from dual;   Gets the time of the sub select To_char (sysdate, ' SS ') as Nowsecond from dual;      Gets the time of the second select To_date (' 2004-05-07 13:23:44 ', ' yyyy-mm-dd hh24:mi:ss ') from DUAL//2. Select To_char (to_date (222, ' J '), ' JSP ') from dual shows both hundred twenty-two 3. Ask for the day of the week 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 Set Date language ALTER SESSION set nls_date_language= ' AMERICAN '; can also be so to_date (' 2002-08-26 ', ' yyyy-mm-dd ', ' Nls_date_language = American ') 4.     Day of the two days Select Floor (sysdate-to_date (' 20020405 ', ' YYYYMMDD ')) from dual;      5. Time NULL usage Select ID, active_date from table1 UNION Select 1, to_date (null) from dual; Note to use To_date (NULL) 6. Month difference a_date between to_date (' 20011201 ', ' YYYYMMDD ') and to_date (' 20011231 ', ' YYYYMMDD ') so December 3      It is not included in this range until 1th noon and 12 o ' 12 o'clock on December 1. So, when time needs to be accurate, think To_char is still necessary 7. Date format conflict problem input format depends on the type of Oracle character set you installed, such as: Us7ascii, the type of date format is: ' 01-JAN-01 ' alter system set Nls_date_language = Am Erican alter session Set Nls_date_language = American or write select To_char in To_date (to_date (' 2002-08-26 ', ' yyy      Y-mm-dd '), ' Day ', ' nls_date_language = American ') from dual;     Notice I'm just lifting nls_date_language and of course there's a lot more to 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 (' 2 002-02-28 ', ' yyyy-mm-dd ')-to_date (' 2002-02-01 ', ' yyyy-mm-dd ') + 1) WHERE To_char (To_da TE (' 2002-02-01 ', ' yyyy-mm-dd ') + RNUM-1, ' D ') Not in (' 1 ', ' 7 ') looks for the number of days from 2002-02-28 to 2002-02-01 that are called before and after Monday and seven respectively Dbms_ UTILITY.     Get_time, let's subtract the result (get 1/100 seconds instead of milliseconds). 9. Find month 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, fo R Format Code D one select To_char (sysdate, ' hh:mi:ss ') time from All_objects Note: The time of the first record is the same as the last row can be built      Set up a function to handle 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. Acquisition of HoursExtract () Find the field value of the date or interval value Select Extract (HOUR from TIMESTAMP ' 2001-02-16 2:38:40 ') from offer sql> select Sysdate, To_c      Har (sysdate, ' hh ') from dual; Sysdate to_char (sysdate, ' HH ')-----------------------------------------2003-10-13 19:35:21 sql> sel      ECT sysdate, To_char (sysdate, ' hh24 ') from dual; Sysdate to_char (sysdate, ' HH24 ')-------------------------------------------2003-10-13 19:35:21 19 1 3. Month and day of processing select Older_date, Newer_date, years, months, ABS (Trunc (Newer_date-add _months (older_date,years*12+months)) days from (select Trunc (Months_between (newer_date, Older_date) /12) years, mod (Trunc (Months_between (Newer_date, older_date)), months, Newer_date, Older_date fr      Om (select HireDate older_date, Add_months (hiredate,rownum) +rownum newer_date from EMP)) 14. Ways to deal with the indefinite number of months Select To_char (Add_months (Last_day (sysdate) +1,-2), ' YYyymmdd '), Last_day (sysdate) from dual 16. Find out the number of days of the Year Select Add_months (trunc (Sysdate, ' year ')-Trunc (sysdate, ' ye        Ar ') from dual the processing method of leap years To_char (Last_day (' To_date (' | |: Year, ' mmyyyy ')), ' DD ') If 28 is not a leap year     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 Clock an interval Select to_date (Floor (To_char (sysdate, ' sssss ')/300) *, ' sssss '), To_char (sysdate, ' sssss ') from dual 2002-11-1 9:55:00 35786 sssss represents 5-bit seconds 20. The first day of the year select To_char (sysdate, ' DDD '), sysdate from dual310 200 2-11-6 10:03:51 21. Calculate hours, minutes, seconds, milliseconds select days, A, TRUNC (a*24) Hours, TRUNC (A*24*60-60*trunc (A * ) Minutes, TRUNC (A*24*60*60-60*trunc (a*24*60)) Seconds, TRUNC (A*24*60*60*100-100*trunc (a*24*60*60)) Mseco NDS from (select Trunc (sysdate) days, Sysdate-trunc (sysdate) A from dual) se Lect * from TabName OrdEr by decode (mode, ' FIFO ', 1,-1) *to_char (RQ, ' Yyyymmddhh24miss '); Floor ((date2-date1)/365) as the annual floor ((date2-date1, 365)/30) as the Month D (MoD (date2-date1, 365), 30) as the day. 23.next_ The day function returns the date of the next week, Days is 1-7 or Sunday-Saturday, 1 means Sunday next_day (sysdate,6) is from the current start of the next Friday.      The following numbers are counted from Sunday onwards. 1 2 3 4 5 6 7th 123456---------------------------------------------------------------Select (sysdate-to_ Date (' 2003-12-03 12:55:45 ', ' yyyy-mm-dd hh24:mi:ss ')) *24*60*60 from ddual date returned is natural after conversion to ss24,round[rounded to the nearest date] (day: Rounded to the nearest Sunday) Select Sysdate s1,round (sysdate) S2, round (sysdate, ' year ') Year,round (sysdate, ' Month ') month, round (                 Sysdate, ' Day ') is truncated from dual25,trunc[to the nearest date, in days], and returns the date type select Sysdate S1, trunc (sysdate) S2, Returns the current date, no time trunc (Sysdate, ' Year ') of year,//returns the current years of January 1, minutes and seconds trunc (sysdate, ' Month ') month,//returns the current month of 1st, Trunc (Sysdate, "Day") Day//returns the current week of Sunday, minutes and minutes from Dual26, returns the latest date in the list of dates select Greatest (' January-January-04 ', ' April-January-04 ', ' 1 0-February -04 ') from dual27. Calculate the time difference Note: The Oracle time difference is in days, so convert to month, day Select Floor (To_number (sysdate-to_date (' 2007-11-02 15:55:03 '), ' Yyyy-mm-dd hh24:mi:ss '))/365) as Spanyears from dual//Time difference-year Select ceil (Moths_between (' 2007-11-02 15:55:03 ', ' yyyy-mm-dd Hh24:mi:ss '))) as spanmonths from dual//Time difference-month Select floor (to_number (sysdate-to_date (' 2007-11 -02 15:55:03 ', ' yyyy-mm-dd Hh24:mi:ss '))) as Spandays from dual//Time difference-days Select Floor (To_number (Sysdate-to_date (' 2007-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 (' 2007-11-02 15:55:03 ', ' yyyy-mm-dd hh24:mi:ss ')) *24*60) as spanminutes from dual//Time difference-min Select floor (to_number (sy Sdate-to_date (' 2007-11-02 15:55:03 ', ' yyyy-mm-dd hh24:mi:ss ')) *24*60*60) as Spanseconds from dual//Time difference-SEC 28. Update Note: o Racle time plus minus is the number of days in units, set the change to N, so converted into years, days 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 N Ewtime from dual//Change Time-When 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-sub Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss '), To_char (SYSDATE+N/24/60/60, ' yyyy -mm-dd hh24:mi:ss ') as NewTime from dual//change time-seconds 29. Find the first day of the month, the last day Select TRUNC (TRUNC (sysdate, ' month ')-1, ' month ') _day_last_month, TRUNC (sysdate, ' month ')-1/86400 last_day_last_month, TRUNC (sysdate, ' month ') FIRST_DAY_CU R_month, Last_day (TRUNC (sysdate, ' MONTH ')) + 1-1/86400 last_day_cur_month from DUAL; This article from the CSDN blog, reproduced please indicate the source: http://b Log.csdn.net/lanmao100/archivE/2010/06/01/5639118.aspx 

　　

Fw:oracle time operation (to be sorted)