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").
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' |
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'