Transfer from: 9CBS
There are a lot of functions on the date, such as: 1. add_months () for adding or decreasing 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-3 Yue -13 SQL> 2, current_date () returns the current date when the current date_value will put area: = 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 -03 04.59.13.668000 afternoon -11: 00 sql> 3, current_timestamp () Returns the current time zone in the TimeStamp WITH TIME ZONE data type current date 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 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 DUAL DBTIMEZONE 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_day (date_value) SQL> SELECT LAST_DAY (DATE '2000-02-01') "Leap Yr? "from dual; leap yr? ---------- 29-February -00 SQL> SELECT LAST_DAY (SYSDATE)" Last Day Of this Month "from Dual; Last Day O ------- --- 30- November -03 SQL> 7, localtimestamp () Return to the date and time in the session TimeStamp_Value: = localtimestamp sql> C Olumn localtimestamp format a28 sql> select localtimestamp from dual; localtimestamp ---------------------------- 13- November -03 12.09.15.43000 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'; sessions have changed.
