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- March -13
SQL>
2, current_date () Returns the current date Date_Value in the current release 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 /
The 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 () The current date to timestamp with time zone data type return to the current discharge area 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 /
The 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 [DateTime_Value | Interval_Value]) 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 () Returns the date and time in the session TimeStamp_Value: = localtimestamp SQL> Column LocalTimeStamp Format A28 SQL> SELECT LOCALTIMESTAMP from DUAL;
LocalTimeStamp ---------------------------- 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 SEXSION SET TIME_ZONE = '- 11:00';
The session has changed.
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 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 () gives a date value, returns the date value of the first time that the day pointed out by the second parameter (the name string should be returned to the corresponding day)
