ADDDATE/DATE_ADD Functions

Description

The ADDDATE function performs an addition or subtraction operation on a specific DATE value; ADDDATE and DATE_ADD are used interchangeably. The return value is a DATETIME or DATETIME type. The DATETIME type is returned in the following cases.

Therefore, to return value of DATETIME type, you should convert the value of first argument by using the CAST function. Even though the date resulting from the operation exceeds the last day of the month, the function returns a valid DATE value considering the last date of the month.

If every argument value of date and time is 0, the return value is determined by the return_null_on_function_errors system parameter; if it is set to yes, then NULL is returned; if it is set to no, an error is returned. The default value is no.

If the calculated value is between '0000-00-00 00:00:00' and '0001-01-01 00:00:00', a value having 0 for all arguments is returned in DATE or DATETIME type. Note that operation in JDBC program is determined by the configuration of zeroDateTimeBehavior, connection URL property (see "API Reference > JDBC API > JDBC Programming > Connection Configuration").

Syntax

ADDDATE(date, INTERVAL expr unit)

DATE_ADD(date, INTERVAL expr unit)

ADDDATE(date, days)

expr value for unit

Unit Value

expr Value

MILLISECOND

MILLISECONDS

SECOND

SECONDS

MINUTE

MINUTES

HOUR

HOURS

DAY

DAYS

WEEK

WEEKS

MONTH

MONTHS

QUARTER

QUARTERS

YEAR

YEARS

SECOND_MILLISECOND

'SECONDS.MILLISECONDS'

MINUTE_MILLISECOND

'MINUTES:SECONDS.MILLISECONDS'

MINUTE_SECOND

'MINUTES:SECONDS'

HOUR_MILLISECOND

'HOURS:MINUTES:SECONDS.MILLISECONDS'

HOUR_SECOND

'HOURS:MINUTES:SECONDS'

HOUR_MINUTE

'HOURS:MINUTES'

DAY_MILLISECOND

'DAYS HOURS:MINUTES:SECONDS.MILLISECONDS'

DAY_SECOND

'DAYS HOURS:MINUTES:SECONDS'

DAY_MINUTE

'DAYS HOURS:MINUTES'

DAY_HOUR

'DAYS HOURS'

YEAR_MONTH

'YEARS-MONTHS'

Example

SELECT SYSDATE, ADDDATE(SYSDATE,INTERVAL 24 HOUR), ADDDATE(SYSDATE, 1);

 

   SYS_DATE    date_add( SYS_DATE , INTERVAL 24 HOUR)   adddate( SYS_DATE , 1)

==============================================================================

  03/30/2010  12:00:00.000 AM 03/31/2010               03/31/2010

 

--it substracts days when argument < 0

SELECT SYSDATE, ADDDATE(SYSDATE,INTERVAL -24 HOUR), ADDDATE(SYSDATE, -1);

 

   SYS_DATE    date_add( SYS_DATE , INTERVAL -24 HOUR)   adddate( SYS_DATE , -1)

==============================================================================

  03/30/2010  12:00:00.000 AM 03/29/2010               03/29/2010

 

--when expr is not fully specified for unit

select sys_datetime, adddate(sys_datetime, interval '1:20' HOUR_SECOND);

 

   SYS_DATETIME                   date_add( SYS_DATETIME , INTERVAL '1:20' HOUR_SECOND)

=======================================================================================

  06:18:24.149 PM 06/28/2010     06:19:44.149 PM 06/28/2010                            

 

SELECT ADDDATE('0000-00-00', 1 );

 

ERROR: Conversion error in date format.

 

-- In JDBC program, java error or depends on 'zeroDateTimeBehavior'

SELECT ADDDATE('0001-01-01 00:00:00', -1);

 

adddate('0001-01-01 00:00:00', -1)

======================

'12:00:00.000 AM 00/00/0000'