DATE_SUB()/SUBDATE() Functions

Description

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

Syntax

DATE_SUB (date, INTERVAL expr unit)

SUBDATE(date, INTERVAL expr unit)

SUBDATE(date, days)

Example

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'