Contents Index XMLGEN function [String] YMD function [Date and time]

ASA SQL Reference
  SQL Functions
    Alphabetical list of functions

YEARS function [Date and time]


Function 

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

Given one date, it returns the year. It is recommended that you use the DATEPART function [Date and time] instead for this purpose.

Given one date and an integer, it adds the integer number of years 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.

Syntax 1 

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

Syntax 2 

YEARS ( datetime-expressioninteger-expression )

Parameters 

datetime-expression    A date and time.

integer-expression    The number of years to be added to the datetime-expression. If integer-expression is negative, the appropriate number of years is subtracted from the datetime value. If you supply an integer expression, the datetime-expression must be explicitly cast as a datetime data type.

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

Usage 

The value of YEARS is calculated from the number of first days of the year between the two dates.

Standards and compatibility 
Example 

The following statements both return -4

SELECT YEARS( '1998-07-13 06:07:12',
              '1994-03-13 08:07:13' )
SELECT DATEDIFF( year,
   '1998-07-13 06:07:12',
   '1994-03-13 08:07:13' )

The following statements return 1998.

SELECT YEARS( '1998-07-13 06:07:12' )
SELECT DATEPART( year, '1998-07-13 06:07:12' )

The following statements return the given date advanced 300 years.

SELECT YEARS(
   CAST( '1998-07-13 06:07:12' AS TIMESTAMP ),
   300 )
SELECT DATEADD( year, 300,
   '1998-07-13 06:07:12' )

Contents Index XMLGEN function [String] YMD function [Date and time]