Caché SQL Reference
YEAR
|
|
A date function that returns the year for a date expression.
Synopsis
YEAR(date-expression)
{fn YEAR(date-expression)}
YEAR takes as input a Caché date integer, an ODBC format date string, or a timestamp.
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 20050231) and out-of-range dates (such as 18300220). 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.
Note:
For compatibility with Caché internal representation of dates, it is
strongly recommended that all year values be expressed as four-digit integers within the range of 1840 through 9999.
The
TO_DATE and
TO_CHAR SQL functions support Julian dates, which can be used to represent years before 1840. ObjectScript provides method calls that support such Julian dates.
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.
This function can also be invoked from ObjectScript using the
YEAR() method call:
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