Caché SQL Reference
DAYOFYEAR
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

A date function that returns the day of the year as an integer for a date expression.
Synopsis
{fn 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.
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 a Caché date integer, a $HOROLOG or $ZTIMESTAMP value, an ODBC format date string, or a timestamp.
A date-expression timestamp is data type %Library.TimeStamp (yyyy-mm-dd hh:mm:ss.fff).
The time portion of the timestamp is not evaluated and can be omitted. The date-expression can also be specified as data type %Library.FilemanDate, %Library.FilemanTimestamp, or %MV.Date.
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 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() method call:
$SYSTEM.SQL.DAYOFYEAR(date-expression)
Examples
The following examples both return the number 64 because the day in the date expression (March 4, 2004) is the 64th day of the year (the leap year day is automatically counted):
SELECT {fn DAYOFYEAR('2004-03-04 12:45:37')} AS DayCount
 
SELECT {fn DAYOFYEAR(59598)} 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