YEAR (SQL)

Synopsis

YEAR(date-expression)
{fn YEAR(date-expression)}

Description

YEAR takes as input a Caché date integer, an ODBC format date string, or a timestamp.

A date-expression timestamp is data type %Library.TimeStampOpens in a new tab (yyyy-mm-dd hh:mm:ss.fff).

The date-expression can also be specified as data type %Library.FilemanDate, %Library.FilemanTimestamp, or %MV.Date.

The year (yyyy) portion should be a four-digit integer in the range 1840 through 9999. There is, however, no validation or range checking for user-supplied dates. YEAR returns the year portion of invalid dates (such as 2005–02–31) and out-of-range dates (such as 1830–02–20). Year values outside the range 1840 through 9999, negative numbers, and fractions are returned as specified. Two digit years are not expanded to four digits.

YEAR returns the corresponding year as a four-digit integer.

YEAR returns zero when the year portion is a string of one or more zeroes (for example '0' or '0000'), or a nonnumeric value. YEAR interprets the initial numeric string encountered as the year value, so omitting the year portion of the date string ('-mm-dd hh:mm:ss'), or omitting the date portion ('hh:mm:ss') results in the first number encountered ('-mm' or 'hh') being treated as the year value. Thus, some placeholder should be supplied for an unknown year value; for compatibility with Caché, 9999 is generally the preferred value.

The year format default is four-digit years. To change this year display default, use the SET OPTION command with the YEAR_OPTION option.

The elements of a datetime string can be returned using the following SQL scalar functions: YEAR, MONTH, DAY, DAYOFMONTH, HOUR, MINUTE, SECOND. The same elements can be returned by using the DATEPART or DATENAME function. DATEPART and DATENAME perform value and range checking on year values.

This function can also be invoked from ObjectScript using the YEAR()Opens in a new tab method call:

$SYSTEM.SQL.YEAR(date-expression)

Examples

The following examples return the integer 2004. No validation is performed:

SELECT YEAR('2004-02-16 12:45:37') AS Year_Given

SELECT {fn YEAR(59590)} AS Year_Given

The following examples return the year as 0 because the year field contains a nonnumeric placeholder. The separator character (–) must be preceded by a some character(s); otherwise the month is returned as the year value:

Asterisk as year placeholder:

SELECT {fn YEAR('*-02-16')} AS Year_Given

Space character as year placeholder:

SELECT YEAR(' -02-16') AS Year_Given

The following example returns the current year:

SELECT YEAR(GETDATE()) AS Year_Now

The following Embedded SQL example returns the current year from two functions. The CURRENT_DATE function returns data type DATE; the NOW function returns data type TIMESTAMP. YEAR returns a four-digit year integer for both input data types:

  &sql(SELECT {fn YEAR(CURRENT_DATE)},
              {fn YEAR({fn NOW()})} INTO :a,:b)
  IF SQLCODE'=0 {
    WRITE !,"Error code ",SQLCODE }
  ELSE {
    WRITE !,"CURRENT_DATE year is: ",a
    WRITE !,"NOW year is: ",b }

The following Embedded SQL example shows that YEAR returns the year portion of an invalid date (the year 2001 was not a leap year) or an out-of-range date:

   SET testdate1="2001-02-29"
   SET testdate2="1835-02-19"
   &sql(SELECT YEAR(:testdate1),
               YEAR(:testdate2) INTO :a,:b)
   IF SQLCODE'=0 {
     WRITE !,"Error code ",SQLCODE }
   ELSE {
     WRITE !,"invalid date returns: ",a
     WRITE !,"out-of-range date returns: ",b }
   QUIT

See Also

• SQL functions: DATENAME, DATEPART, DAYOFYEAR, QUARTER, WEEK, TO_DATE

• ObjectScript function: $ZDATE