DAYS function [Date and time]

Manipulates a TIMESTAMP or returns the number of days between two TIMESTAMP values. For specific details, see the Remarks section below.

Syntax 1

DAYS( timestamp-expression )

Syntax 2

DAYS( timestamp-expression, timestamp-expression )

Syntax 3

DAYS( timestamp-expression, integer-expression )

Parameters

timestamp-expression A TIMESTAMP value.
integer-expression The number of days to be added to the timestamp-expression. If the integer-expression is negative, the appropriate number of days is subtracted from timestamp-expression. If you supply an integer expression, the timestamp-expression must be explicitly cast as a TIME, DATE or TIMESTAMP. If timestamp-expression is a TIME value, the current date is assumed.
For information about casting data types, see CAST function [Data type conversion].

Returns

Remarks

The result of the DAYS function depends on its arguments. The DAYS function ignores hours, minutes, and seconds in its arguments.

Syntax 1 If you pass a single timestamp-expression to the DAYS function, it will return the number of days between 0000-02-29 and timestamp-expression as an INTEGER.

Note

0000-02-29 is not meant to imply an actual date; it is the default date used by the DAYS function.
Syntax 2 If you pass two TIMESTAMP values to the DAYS function, the function returns the integer number of days between them.
Syntax 3 If you pass a TIMESTAMP value and an integer to the DAYS function, the function returns the TIMESTAMP result of adding the integer number of days to the timestamp-expression argument.

Instead of Syntax 2, use the DATEDIFF function. Instead of Syntax 3, use the DATEADD function.

Example

The following statement returns the integer 729889:

SELECT DAYS( '1998-07-13 06:07:12' );

The following statements return the integer value -366, indicating that the second DATE value is 366 days before the first. It is recommended that you use the second example (DATEDIFF):

SELECT DAYS( '1998-07-13 06:07:12',
             '1997-07-12 10:07:12' );

SELECT DATEDIFF( day,
   '1998-07-13 06:07:12',
   '1997-07-12 10:07:12' );

The following statements return the TIMESTAMP value 1999-07-14 00:00:00.000. It is recommended that you use the second example (DATEADD):

SELECT DAYS( CAST('1998-07-13' AS DATE ), 366 );

SELECT DATEADD( day, 366, '1998-07-13' );

Discuss this page in DocCommentXchange.

DAYS function [Date and time]

Note