Skip to main content

DAYOFYEAR (SQL)

A date function that returns the day of the year as an integer for a date expression.

Synopsis

{fn DAYOFYEAR(date-expression)}

Description

DAYOFYEAR returns an integer from 1 to 366 that corresponds to the day of the year for a given date expression. DAYOFYEAR calculates leap year dates.

The day of year is calculated for an InterSystems IRIS date integer, a $HOROLOG or $ZTIMESTAMP value, an ODBC format date string, or a timestamp.

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 time portion of the timestamp is not evaluated and can be omitted.

When calculating day of the month for a $HOROLOG value, DAYOFYEAR calculates leap years differences, including century day adjustments: 2000 is a leap year, 1900 and 2100 are not leap years.

DAYOFYEAR can process date-expression values prior to December 31, 1840 as negative integers. This is shown in the following example:

SELECT {fn DAYOFYEAR(-306)} AS LastDayFeb,  /* February 29, 1840 */
       {fn DAYOFYEAR(-305)} AS FirstDayMar  /* March 1, 1840     */

The earliest valid date-expression is -672045 (January 1, 0001).

The same day count can be returned by using the DATEPART or DATENAME function. DATEPART and DATENAME performs value and range checking on date expressions.

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

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

Arguments

date-expression

A date expression that is the name of a column, the result of another scalar function, or a date or timestamp literal.

Examples

The following examples both return the number 64 because the day in the date expression (March 4, 2016) is the 64th day of the year (the leap year day is automatically counted):

SELECT {fn DAYOFYEAR('2016-03-04 12:45:37')} AS DayCount
SELECT {fn DAYOFYEAR(63981)} AS DayCount

The following examples all return the count for the current day:

SELECT {fn DAYOFYEAR({fn NOW()})} AS DNumNow,
       {fn DAYOFYEAR(CURRENT_DATE)} AS DNumCurrD,
       {fn DAYOFYEAR(CURRENT_TIMESTAMP)} AS DNumCurrTS,
       {fn DAYOFYEAR($HOROLOG)} AS DNumHorolog,
       {fn DAYOFYEAR($ZTIMESTAMP)} AS DNumZTS

Note that $ZTIMESTAMP returns Coordinated Universal Time (UTC). The other time-expression values return the local time. This may affect the DAYOFYEAR value.

The following example uses a subquery to return Employee records ordered by the day of year of each person’s birthday:

SELECT Name,DOB
FROM (SELECT Name,DOB,{fn DAYOFYEAR(DOB)} AS BDay FROM Sample.Employee)
ORDER BY BDay

See Also

FeedbackOpens in a new tab