Returns a 4-digit number corresponding to the year of a given date/time, returns the number of years between two specified date/times, or adds the specified integer-expression number of years to a date/time.
YEARS ( datetime-expression
| datetime-expression, datetime-expression
| datetime-expression, integer-expression )
Parameter |
Description |
|---|---|
datetime-expressio |
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 are subtracted from the datetime value. If you supply an integer expression, the datetime-expression must be explicitly cast as a DATETIME data type. |
Syntax 1 returns an INTEGER.
Syntax 2 returns a TIMESTAMP.
The following statement returns the value 1998:
SELECT YEARS( '1998-07-13 06:07:12' ) FROM iq_dummy
The following statement returns the value 2, to signify the difference between the two dates.
SELECT YEARS( '1997-07-13 06:07:12', '1999-09-13 10:07:12' ) FROM iq_dummy
The following statement returns the YEARS(cast('1999-05-12 21:05:07' as timestamp), 5) value 2004-05-12 21:05:07.000:
SELECT YEARS( CAST( '1999-05-12 21:05:07' AS TIMESTAMP ), 5) FROM iq_dummy
The first syntax of the YEARS function is the same as the YEAR function.
The second syntax returns the number of years from the first date to the second date, calculated from the number of first days of the year between the two dates. The number might be negative. Hours, minutes, and seconds are ignored. For example, the following statement returns 2, which is the number of first days of the year between the specified dates:
SELECT YEARS ( '2000-02-24', '2002-02-24' ) FROM iq_dummy
The next statement also returns 2, even though the difference between the specified dates is not two full calendar years. The value 2 is the number of first days of the year (in this case January 01, 2001 and January 01, 2002) between the two dates.
SELECT YEARS ( '2000-02-24', '2002-02-20' ) FROM iq_dummy
The third syntax adds an integer-expression number of years to the given date. If the new date is past the end of the month (such as SELECT YEARS ( CAST ( ‘1992-02-29’ AS TIMESTAMP ), 1 )), the result is set to the last day of the month. If integer-expression is negative, the appropriate number of years is subtracted from the date. Hours, minutes, and seconds are ignored.