DAYS function [Date and time]

The DAYS function 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.

Standards and compatibility

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