Introduction to the Oracle date function

The Oracle date function is used to process the date and time in the Oracle database. Here is a detailed introduction to the use of the Oracle date function.

(1) SYSDATE and TRUNC

The two Oracle date functions take the date of the Oracle server system and the ability to truncate the decimal part. Observe the following operations:

create table test_date (name varchar2(20), p_date date);
insert into test_date values (' name1 ', sysdate);
select * from test_date;
select * from test_date where p_date=' 25-october-05 ';
select * from test_date where trunc(p_date)= '25-10-05';

In the Oracle system, SYSDATE is used to obtain not only the date but also the time information. The time information is actually the fractional part of the Julian day data.

(2) ADD_MONTHS

The Oracle date function returns a date with a month apart from the date provided. The function gives the number of future or previous months. The syntax is as follows:

ADD_MONTHS (start date, add or subtract months)

select add_months(' 26-october-05 ',2) from dual;
select add_months(' 26-october-05 ',-2) from dual;

(3) the LAST_DAY

Returns the last day of the month containing the given date. Grammar:

LAST_DAY (date)

select last_day(' 21-2-80 ') from dual;

(4) MONTHS_BETWEEN

Returns the month between two days. Grammar:

MONTHS_BETWEEN (later date, earlier date)
select months_between(' 12-october-05 ',' 12-september-03 ') from dual;

The following is 1 some supplementary information, although a bit narrow, but reference

There are a lot of date functions in oracle,

Such as:

date_value:=add_months(date_value,number_of_months) example: SQL > select add_months(sysdate,12) Next Year from dual; Next Year -- 13-11-04 SQL > select add_months(sysdate,112) Last Year from dual; Last Year March 13-13 SQL >

2. current_date() returns the current date in the current time zone. date_value:=current_date SQL > column sessiontimezone for a15 SQL > select sessiontimezone, current_date from dual; SESSIONTIMEZONE CURRENT_DA -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 13 - November - 03 SQL 08:00 > alter session set time_zone= -11:002 / session changed. SQL > select sessiontimezone, current_timestamp from dual; SESSIONTIMEZONE CURRENT_TIMESTAMP -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- November 11-12-03 04.59.13.668000 afternoon - 11:00 SQL >

3. current_timestamp() returns the current date in the current time zone as 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 -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + 13 - November - 03 11.56.28.160000 08:00 morning 8:00 SQL > alter session set time_zone= -11:002 / session changed. SQL > select sessiontimezone, current_timestamp from dual; SESSIONTIMEZONE CURRENT_TIMESTAMP -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- November 11-12-03 04.58.00.243000 afternoon - 11:00 SQL >

4. dbtimezone() returns to the time zone varchar_value:=dbtimezone SQL > select dbtimezone from dual; DBTIME ------ -07:00 SQL >

5, extract() 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) 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

6, last_day() returns the date of the last day of the month with the date parameter date_value:=last_day(date_value) SQL > select last_day (date2000-02-01) Leap Yr & # 63; from dual; Leap Yr & # 63; -- February 29 -- 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 -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - November 13-03 12.09.15.433000 SQL in the afternoon > select localtimestamp, current_timestamp from dual; LOCALTIMESTAMP CURRENT_TIMESTAMP -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - November 13-03 12.09.31.006000 + 08 November 13 - - 03 12.09.31.006000 afternoon: SQL at 00 p.m. > alter session 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 13-11-2003-03 05.11.31.259000 12:11:31 SQL PM this afternoon >

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, return the date value indicated by the second parameter that appears in the first time (should return the name string of the corresponding date)