Oracle date and time functions summary 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 1998 24 The time range for hours format is: 0:00:00 - 23:59:59 .... 12 hours format Under time range: 1:00:00 - 12:59:59 ... 1. Date and characters Conversion function method (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_LANGUAGE =' American '; can also be to_date ('
2002-08-26
',' YYYY-MM-DD ',' NLS_DATE_LANGUAGE = American ') 4. The days during the two days Select Floor (sysdate - to_date (' 20020405 ',' YYYMMDD ')) from Dual; 5. Time is NULL usage select id, active_date from table1 UNION select 1, TO_DATE (null) from dual; note that use TO_DATE (null) 6. a_date between to_date ( '20011201', 'yyyymmdd') and to_date ( '20011231', 'yyyymmdd') so It is not included in this range before 12 o'clock on December 31 and 12 o'clock on December 1. 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 or writes Select to_char in To_Date (to_date "
2002-08-26
',' YYYY-MM-DD '),' Day ',' NLS_DATE_LANGUAGE = American ') from Dual; Note I just got NLS_DATE_LANGUAGE, of course there are still a lot, 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 ('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
In addition to the days and seven days, DBMS_UTILITY.GET_TIME is called before and after, the result is subtracted (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-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 first record of TIME is the same as the last line. may establish a function to handle the 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 hours 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_c HAR (sysdate, 'hh24') ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ - 2003-10-13 19:35:21 19 Get the Year of the Year and this Similar 13. Employment 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)), 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 months to deactivate SELECT TO_CHAR (Add_MontHS (Last_Day (sysdate) 1, -2), '
YYYYMMDD '), Last_day (sysdate) from Dual 16. Finding 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 '||),' DD ') If it is 28, it is not a leap year 17. The difference between YYYY and RRRR' YYYY99 TO_C ---------- YYYY 99 0099 RRRR 99 1999 YYYY 01 0001 RRRR 01 2001 18. Processing of different time zones Select TO_CHAR (New_TIME (Sysdate, 'GMT', 'Est'), 'DD / MM / YYYY HH: MI: SS'), SYSDATE AROAL; 19 . 5 seconds a spacing select to_date (floor (to_char (sysdate, 'ssss') / 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'), SESDATE, SISDATE, SISDATE, SESDATE, SESDATE, SESDATE 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, 'YYYYMMDDHH24MISS'); // Floor ((Date2-Date1) / 365) As an annual Floor (Date2-Date1, 365) / 30) as Month MOD (MOD (Date2 Date1, 365), 30) As the day. 23.Next_day function next_day (sysdate, 6) is from the next Friday next Friday. The numbers behind are counted from Sunday. 1 2 3 4 5 6 7 Days and Three Four Version
There are many functions on the date on the date on the date on Oracle. For example, add_months () is used to increase or decrease from one date value or reduce some month Date_Value: = add_months (Date_Value, Number_OF_MONTHS) Example: SQL> SELECT Add_MONTHS (Sysdate, 12) "Next Year" from Dual; Next Year ---------- 13- November -04 SQL> SELECT Add_Months (sysdate, 112) "Last Year" from dual; last year - --------- 13- March -13 SQL> 2, current_date () Returns the current date Date_Value in the current time zone: = current_date sql> Column sessionTIMEZONE for A15 SQL> SELECT sessionTIMEZONE, CURRENT_DATE FROM DUAL SessionTimezone Current_da ------------------------ 08: 00 13- November -03 SQL> ALTER session set time_zone = '- 11:00' 2 / Session has changed. SQL> SELECT SessionTimezone, Current_TimeStamp from Dual; SessionTimezone Current_TimeStamp ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------ -11: 00 12- November -0304.59.13
.668000 pm -11: 00 SQL> 3, current_timestamp () when the current date to timestamp with time zone data type returns the current zone will be put in timestamp_with_time_zone_value: = current_timestamp ([timestamp_precision]) SQL> column sessiontimezone for a15 SQL> column current_timestamp format A36 SQL> SELECT SessionTimezone, Current_TimeStamp from Dual; SessionTimezone Current_TimeStamp -------------------------------------- ------------- 08: 00 13- November -03 11.56.28.160000 Morning 08: 00 SQL> ALTER Session Set Time_ZONE = '- 11:00' 2 / Session has changed. SQL> SELECT SessionTimezone, Current_TimeStamp from Dual; SessionTimezone Current_TimeStamp ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------ -11: 00 12- November -03 04.58.00.243000 afternoon -11: 00 sql> 4, dbtimezone () Returns time zone varchar_value: = dbtimezone sql> select dbtimezone from dual; DBTime ------ -07: 00 SQL> 5, extract () Field value Date_Value: = extract (Date_field from ") SQL> SELECT Extract (Month from sysdate" This Month "from Dual; this Month ---------- 11 SQL> SELECT EXTRACT (Year from add_months (sysdate, 36))" 3 years out "from dual; 3 years Out -------- ---- 2006 SQL> 6, last_day () returns the date Date_Value: = last_value) SQL> SELECT LAST_DAY (Date'2000-02-01)
') "Leap Yr?" From dual; leap yr? -------------------------------------------------------------------------------------------------------- ------------------------------------ ------------------------- 13- November -03 12.09.15.433000 afternoon SQL> SELECT LOCALTIMESTAMP, CURRENT_TIMESTAMP from Dual; LOCALTIMESTAMP CURRENT_TIMESTAMP ------ -------------------------------------------------------------------------------------------------------------------------------------------- ---------- 13- November -03 12.09.31.006000 13- November -03 12.09.31.006000 afternoon 08: afternoon 00 SQL> ALTER session set time_zone = '- 11:00'; session change. SQL> SELECT LOCALTIMESTAMP, TO_CHAR (SYSDATE, 'DD-MM-YYYY HH: MI: SS AM') "Sysdate" from Dual; LocalTimeStamp Sysdate ------------------- ---------------------------------- 12- November -03 05.11.31.259000 13-11-2003 12:11 : 31 afternoon SQL> 8, MONTHS_BETWEEN () Judging the number of months between the two dates Number_Value: = MONTHS_BETWEEN (Date_Value, Date_Value) SQL> SELECT MONTHS_BETWEEN (sysdate, date'1971-05-18
') from dual; months_between (sysdate, date "
1971-05-18
') ---------------------------------------- 389.855143 SQL> SELECT MONTHS_BETWEEN (Sysdate, Date '
2001-01-01
') from dual; months_between (sysdate, date "
2001-01-01
') ---------------------------------------- 34.4035409 SQL> 9, Next_DAY () A date value is given, and the date value indicated by the second parameter indicates (the name string should be returned to the corresponding day)
