8.9.3 DAYOFYEAR

Returns the specified date as the number of days elapsed since January 1 of that year.

Organization of this subsection

(1) Specification format

scalar-function-DAYOFYEAR ::= {DAYOFYEAR|DOY}(target-data)
 
  target-data ::= value-expression

Note: DOY can be used as an abbreviated form for DAYOFYEAR.

target-data:

Specifies the data representing the day.

The following rules apply:

Specify the target data in the form of a value expression. For details about value expressions, see 7.20 Value expression.
The data type of the target data must be DATE, TIMESTAMP, CHAR, or VARCHAR. In the case of CHAR or VARCHAR, you must specify a character string literal that adheres to the predefined input representation formats. For details about the predefined input representations, see 6.3.3 Predefined character-string representations.
You cannot specify a dynamic parameter by itself for the target data.

The following example illustrates the result of executing the scalar function DAYOFYEAR.

Example

Return the number of days elapsed in the year associated with the date January 15, 2013.

DAYOFYEAR(DATE'2013-01-15') → 15

The value of the execution result will be an integer value from 1 to 366 representing the number of days elapsed since January 1 of that year.
The data type of the execution result will be INTEGER.
The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).
If the target data has the null value, the execution result will be a null value.

Example:

Determine the number of days elapsed in the year associated with target data in column C2 of table T1.

SELECT "C1",DAYOFYEAR("C2") FROM "T1"

[Figure]