Introduction to Oracle Date functions _oracle

Oracle date functions are used to process dates and times in an Oracle database, and the following is a detailed introduction to the use of Oracle date functions in the hope of enlightening you.

(1) Sysdate and trunc

Two Oracle date functions take the date of the Oracle Server system and the function of truncating the fractional part. Observe the following actions:

CREATE TABLE Test_date (name varchar2, p_date date);
INSERT into test_date values (' name1 ', sysdate);
SELECT * from Test_date;
SELECT * from Test_date where p_date= ' 2 May-October-05 ';
SELECT * from Test_date where trunc (p_date) = ' 2 May-October-05 ';

The Oracle system uses Sysdate to not only contain the date but also contains the time information, the time information is actually represents the Julian day data in the decimal part.

(2) Add_months

The Oracle Date function returns a date with a month that differs from the supplied date, and the number of months in the future or previous month is given in the function. The syntax is as follows:

Add_months (starting date, increase or decrease in number of months)

Select Add_months (' 2 June-October -05 ', 2) from dual;
Select Add_months (' 2 June-October -05 ', -2) from dual;

(3) Last_day

Returns the last day of the month that contains the given date. The syntax is:

Last_day (date)

Select Last_day (' 2 January-February -80 ') from dual;

(4) Months_between

Returns the month of a two-day period. The syntax is:

Months_between (late date, earlier date)
Select Months_between (' December-October-05 ', ' December-September -03 ') from dual;

Here are some additional information, albeit a little bit, but refer to the following

There are many functions about dates in Oracle,

Such as:

1, Add_months () is used to increase or reduce some months from a date value date_value:=add_months (date_value,number_of_months) Example: sql> Select Add_months (    sysdate,12) Next year from dual;    Next year----------1 March-November -04 sql> Select Add_months (sysdate,112) last year from dual; Last year----------1 March-March -13 sql>

 2, Current_date () returns the current date that will be placed in the time zone  date_value:=current_date SQL> column  sessiontimezone for a15 sql> select sessiontimezone,current_date from  dual;    sessiontimezone current_da --------------- ---------- +08:00           13-11 Month -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-11 Month -03  04.59.13.668000  pm  -11:                   00    sql>      

 

3, Current_timestamp () returns the current date in the time zone by Timestamp with time zone data type  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-11 month -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-11 Month -03 04.58.00.243000  PM  -11:                   00    SQL>     

4, Dbtimezone () returns the time zone Varchar_value:=dbtimezone sql> select Dbtimezone from dual; DBTIME-------07:00 sql>

5. Extract () To find the field value of the date or interval value date_value:=extract (Date_field from [datetime_value|interval_value]) sql> Select Extract (    Month from Sysdate-month from dual;    This Month----------one sql> Select Extract (Year from add_months (sysdate,36)) 3 Years out from dual; 3 Years out-----------2006

 6, Last_day () returns the date of the last day of the month that contains the date parameter  date_value:=last_day (date_value)  SQL> select  Last_day (date2000-02-01)  Leap Yr? from dual;    Leap Yr? --- ------- 29-2 Month  -00    sql> select last_day (sysdate)  Last  day of this month from dual;    last day o ------- --- 30-11 month -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-11 Month -03 12.09.15.433000  afternoon     SQL> select localtimestamp,current_timestamp from dual;     LOCALTIMESTAMP                CURRENT_TIMESTAMP ---------------------------- ------------------------------------  13-11 month -03 12.09.31.006000   13-11 month -03 12.09.31.006000  afternoon  +08:  afternoon                           00    sql> alter session set  time_zone=-11:00;&nThe bsp;    session has changed.     sql> select localtimestamp,to_char (sysdate,dd-mm-yyyy hh:mi:ss  AM)  SYSDATE from dual;    LOCALTIMESTAMP                SYSDATE ---------------------------- --- --------------------- 12-11 Month -03 05.11.31.259000   13-11-2003 12:11:31  afternoon   PM     SQL>      

 8, Months_between () determines the number of months between two dates  number_value:=months_between (date_value,date_value)  SQL>  select months_between (sysdate,date1971-05-18)  from dual;    months_ BETWEEN (sysdate,date1971-05-18)  ----------------------------------------                                  389.855143    sql> select  months_between (sysdate,date2001-01-01)  from dual;    months_between ( sysdate,date2001-01-01)  ----------------------------------------                                  34.4035409    SQL>       

 9, Next_day () given a date value, returns the date value indicated by the second argument for the first time (the name string of the corresponding date should be returned)   This article refers to Oracle China Alliance