Datetime Functions

Datetime Functions

Datetime functions operate on values of the DATE datatype. All datetime functions return a datetime or interval value of DATE datatype, except the MONTHS_BETWEEN function, which returns a number. The datetime functions are:

ADD_MONTHS

Purpose

ADD_MONTHS returns the date d plus n months. The argument n can be any integer. If d is the last day of the month or if the resulting month has fewer days than the day component of d, then the result is the last day of the resulting month. Otherwise, the result has the same day component as d.

Examples

select add_months(to_date('2010/01/27','yyyy/mm/dd'),1) from dual

-------------------

2010-2-27

 

select add_months(to_date('2010/01/28','yyyy/mm/dd'),1) from dual

-------------------

2010-2-28

 

select add_months(to_date('2010/01/29','yyyy/mm/dd'),1) from dual

-------------------

2010-2-28

 

select add_months(to_date('2010/01/31','yyyy/mm/dd'),1) from dual

-------------------

2010-2-28

 

LAST_DAY

Purpose

LAST_DAY returns the date of the last day of the month that contains date.

Examples

select last_day(to_date('2010/01/15','yyyy/mm/dd')) from dual

-------------------

2010-1-31

 

 

select last_day(to_date('2010/01','yyyy/mm')) from dual

-------------------

2010-1-31

 

MONTHS_BETWEEN

Purpose

MONTHS_BETWEEN returns number of months between dates date1 and date2. If date1 is later than date2, then the result is positive. If date1 is earlier than date2, then the result is negative. If date1 and date2 are either the same days of the month or both last days of months, then the result is always an integer. Otherwise Oracle calculates the fractional portion of the result based on a 31-day month and considers the difference in time components date1 and date2.

Examples

SELECT MONTHS_BETWEEN(TO_DATE('2010/05/29','yyyy/mm/dd'),TO_DATE('2010/04/30','yyyy/mm/dd') ) from dual

-------------------

0.967741935483871

 

SELECT MONTHS_BETWEEN(TO_DATE('2010/05/30','yyyy/mm/dd'),TO_DATE('2010/04/30','yyyy/mm/dd') ) from dual

-------------------

1

 

SELECT MONTHS_BETWEEN(TO_DATE('2010/05/31','yyyy/mm/dd'),TO_DATE('2010/04/30','yyyy/mm/dd') ) from dual

-------------------

1

 

SYSDATE

Purpose

SYSDATE returns the current date and time. The datatype of the returned value is DATE. The function requires no arguments. In distributed SQL statements, this function returns the date and time on your local database. You cannot use this function in the condition of a CHECK constraint.

Examples

SELECT sysdate FROM DUAL;

-------------------

2010-10-12 12:02:42

SYSTIMESTAMP

Purpose

SYSTIMESTAMP returns the system date, including fractional seconds and time zone of the system on which the database resides. The return type is TIMESTAMP WITH TIME ZONE.

Examples

SELECT systimestamp FROM DUAL;

-------------------

12-OCT-10 11.59.58.650217 AM +08:00