Skip to main content

YEAR (SQL)

A date function that returns the year for a date expression.

Synopsis

YEAR(date-expression)

{fn YEAR(date-expression)}

Description

YEAR takes as input an InterSystems IRIS date integer ($HOROLOG date), an ODBC format date string, or a timestamp. YEAR returns the corresponding year as an integer.

A date-expression timestamp can be either data type %Library.PosixTimeOpens in a new tab (an encoded 64-bit signed integer), or data type %Library.TimeStampOpens in a new tab (yyyy-mm-dd hh:mm:ss.fff).

The year (yyyy) portion should be a four-digit integer in the range 0001 through 9999. Leading zeros are optional on input. Leading zeros are suppressed on output. Two digit years are not expanded to four digits.

The date portion of date-expression is validated and must include a month within the range 1 through 12 and a valid day value for the specified month and year. Otherwise, an SQLCODE -400 error <ILLEGAL VALUE> is generated.

The time portion of date-expression is validated if present, but can be omitted.

Note:

For compatibility with InterSystems IRIS internal representation of dates, it is strongly recommended that all year values be expressed as four-digit integers within the range of 0001 through 9999.

The TO_DATE and TO_CHAR SQL functions support “Julian dates,” which can be used to represent years before 0001. ObjectScript provides method calls that support such Julian dates.

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.

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

$SYSTEM.SQL.Functions.YEAR(date-expression)

Arguments

date-expression

An expression that evaluates to either an InterSystems IRIS date integer, an ODBC date string, or a timestamp. This expression can be the name of a column, the result of another scalar function, or a date or timestamp literal.

Examples

The following examples return the integer 2018:

SELECT YEAR('2018-02-22 12:45:37') AS ODBCDate_Year
SELECT {fn YEAR(64701)} AS HorologDate_Year

The following example returns the current year:

SELECT YEAR(GETDATE()) AS Year_Now

The following 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:

SELECT {fn YEAR(CURRENT_DATE)}, {fn YEAR({fn NOW()})}

See Also

FeedbackOpens in a new tab