DAYS function [Date and time]

ASA SQL Reference
SQL Functions
Alphabetical list of functions

DAYS function [Date and time]

Function

Given a single date, this function returns the number of days since 0000-02-29.

Given two dates, this function returns the integer number of days between them. It is recommended that you use the DATEDIFF function [Date and time] instead for this purpose.

Given one date and an integer, it adds the integer number of days to the specified date. It is recommended that you use the DATEADD function [Date and time] instead for this purpose.

Syntax 1 returns an integer. Syntax 2 returns a timestamp.

DAYS ignores hours, minutes, and seconds.

Syntax 1

DAYS ( [ datetime-expression, ] datetime-expression )

Syntax 2

DAYS ( datetime-expression, integer-expression )

Parameters

datetime-expression A date and time.

integer-expression The number of days to be added to the datetime-expression. If the integer-expression is negative, the appropriate number of days is subtracted from the timestamp. If you supply an integer expression, the datetime-expression must be explicitly cast as a date or timestamp.

For information on casting data types, see CAST function [Data type conversion].

Standards and compatibility

SQL/92 Vendor extension.
SQL/99 Vendor extension.
Sybase Not supported by Adaptive Server Enterprise.

Example

The following statement returns the integer 729 889.

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

The following statement returns the current Julian day.

SELECT DAYS( CURRENT DATE ) + 1721119

The following statements return the integer value -366, indicating that the second date is 366 days prior to 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 1999-07-14 00:00:00.0. 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' )