The DATE_SUB and SUBDATE() are used interchangeably, and they perform an addition or subtraction operation on a specific DATE value. The value is returned in DATE or DATETIME type. If 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").
DATE_SUB (date, INTERVAL expr unit)
SUBDATE(date, INTERVAL expr unit)
SUBDATE(date, days)
SELECT SYSDATE, SUBDATE(SYSDATE,INTERVAL 24 HOUR), SUBDATE(SYSDATE, 1);
SYS_DATE date_sub( SYS_DATE , INTERVAL 24 HOUR) subdate( SYS_DATE , 1)
==============================================================================
03/30/2010 12:00:00.000 AM 03/29/2010 03/29/2010
--it adds days when argument < 0
SELECT SYSDATE, SUBDATE(SYSDATE,INTERVAL -24 HOUR), SUBDATE(SYSDATE, -1);
SYS_DATE date_sub( SYS_DATE , INTERVAL -24 HOUR) subdate( SYS_DATE , -1)
==============================================================================
03/30/2010 12:00:00.000 AM 03/31/2010 03/31/2010
SELECT SUBDATE('0000-00-00 00:00:00', -50);
ERROR: Conversion error in date format.
SELECT SUBDATE('0001-01-01 00:00:00', 10);
subdate('0001-01-01 00:00:00', 10)
==============================
'12:00:00.000 AM 00/00/0000'