YEARS function [Date and time]

Returns a four 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 )

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 timestamp 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 may 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.