Date Functions

Date functions operate on values of the DATE datatype. All date functions return a value of DATE datatype, except the MONTHS_BETWEEN function, which returns a number.

ADD_MONTHS

 

Syntax  

 
ADD_MONTHS(d,n)
 

Purpose  

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.

 

Example  

 
SELECT TO_CHAR(
     ADD_MONTHS(hiredate,1),
     'DD-MON-YYYY') "Next month"
     FROM emp 
     WHERE ename = 'SMITH';

Next Month
-----------
17-JAN-1981
 

LAST_DAY

 

Syntax  

 
LAST_DAY(d)
 

Purpose  

Returns the date of the last day of the month that contains d. You might use this function to determine how many days are left in the current month.

 

Example 1  

 
SELECT SYSDATE,
   LAST_DAY(SYSDATE) "Last",
   LAST_DAY(SYSDATE) - SYSDATE "Days Left"
   FROM DUAL;
 
SYSDATE   Last       Days Left
--------- --------- ----------
23-OCT-97 31-OCT-97          8
 

Example 2  

 
SELECT TO_CHAR(
  ADD_MONTHS(
    LAST_DAY(hiredate),5),
     'DD-MON-YYYY') "Five months"
  FROM emp 
  WHERE ename = 'MARTIN';
 
Five months
-----------
28-FEB-1982
 

MONTHS_BETWEEN

 

Syntax  

 
MONTHS_BETWEEN(d1, d2)
 

Purpose  

Returns number of months between dates d1 and d2. If d1 is later than d2, result is positive; if earlier, negative. If d1 and d2 are either the same days of the month or both last days of months, 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 of d1 and d2.

 

Example  

 
SELECT MONTHS_BETWEEN 
   (TO_DATE('02-02-1995','MM-DD-YYYY'),
    TO_DATE('01-01-1995','MM-DD-YYYY') ) "Months"
    FROM DUAL;
 
    Months
----------
1.03225806
 

NEW_TIME

 

Syntax  

 
NEW_TIME(d, z1, z2)
 

Purpose  

Returns the date and time in time zone z2 when date and time in time zone z1 are d. The arguments z1 and z2 can be any of these text strings:

 
 
 

AST

ADT

 

Atlantic Standard or Daylight Time  
 
 

BST

BDT

 

Bering Standard or Daylight Time  
 
 

CST

CDT

 

Central Standard or Daylight Time  
 
 

EST

EDT

 

Eastern Standard or Daylight Time  
 
 

GMT

 

Greenwich Mean Time  
 
 

HST

HDT

 

Alaska-Hawaii Standard Time or Daylight Time.  
 
 

MST

MDT

 

Mountain Standard or Daylight Time  
 
 

NST

 

Newfoundland Standard Time  
 
 

PST

PDT

 

Pacific Standard or Daylight Time  
 
 

YST

YDT

 

Yukon Standard or Daylight Time  

NEXT_DAY

 

Syntax  

NEXT_DAY(d, char)  

Purpose  

Returns the date of the first weekday named by char that is later than the date d. The argument char must be a day of the week in your session's date language-either the full name or the abbreviation. The minimum number of letters required is the number of letters in the abbreviated version; any characters immediately following the valid abbreviation are ignored. The return value has the same hours, minutes, and seconds component as the argument d.  

Example  

This example returns the date of the next Tuesday after March 15, 1992.

SELECT NEXT_DAY('15-MAR-92','TUESDAY') "NEXT DAY"
     FROM DUAL;
 
NEXT DAY
---------
17-MAR-92
 

ROUND

 

Syntax  

 
ROUND(d[,fmt])
 

Purpose  

Returns d rounded to the unit specified by the format model fmt. If you omit fmt, d is rounded to the nearest day. See "ROUND and TRUNC" for the permitted format models to use in fmt.

 

Example  

 
SELECT ROUND (TO_DATE ('27-OCT-92'),'YEAR')
   "New Year" FROM DUAL;
 
New Year
---------
01-JAN-93
 

SYSDATE

 

Syntax  

 
SYSDATE
 

Purpose  

Returns the current date and time. 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.

 

Example  

 
SELECT TO_CHAR
    (SYSDATE, 'MM-DD-YYYY HH24:MI:SS')"NOW"
     FROM DUAL;
NOW
-------------------
10-29-1993 20:27:11
 

TRUNC

 

Syntax  

 
1TRUNC(d,[fmt])
 

Purpose  

Returns d with the time portion of the day truncated to the unit specified by the format model fmt. If you omit fmt, d is truncated to the nearest day. See "ROUND and TRUNC" for the permitted format models to use in fmt.

 

Example  

 
SELECT TRUNC(TO_DATE('27-OCT-92','DD-MON-YY'), 'YEAR')
  "New Year" FROM DUAL;
 
New Year
---------
01-JAN-92
 

ROUND and TRUNC

Table 3-11 lists the format models you can use with the ROUND and TRUNC date functions and the units to which they round and truncate dates. The default model, 'DD', returns the date rounded or truncated to the day with a time of midnight.

Table 3-11 Date Format Models for the ROUND and TRUNC Date Functions
Format Model   Rounding or Truncating Unit   
CC
SCC
 

One greater than the first two digits of a four-digit year.  

 
SYYYY
YYYY
YEAR
SYEAR
YYY
YY
Y
 

Year (rounds up on July 1)  

 
IYYY
IY
IY
I
 

ISO Year  

 
Q
 

Quarter (rounds up on the sixteenth day of the second month of the quarter)  

 
MONTH
MON
MM
RM
 

Month (rounds up on the sixteenth day)  

 
WW
 

Same day of the week as the first day of the year.  

 
IW
 

Same day of the week as the first day of the ISO year.  

 
W
 

Same day of the week as the first day of the month.  

 
DDD
DD
J
 

Day  

 
DAY
DY
D
 

Starting day of the week  

 
HH
HH12
HH24
 

Hour  

 
MI
 

Minute  

 

The starting day of the week used by the format models DAY, DY, and D is specified implicitly by the initialization parameter NLS_TERRITORY. For information on this parameter, see Oracle8 Reference.