DAYOFMONTH
Synopsis
{fn DAYOFMONTH(date-expression)}
Arguments
Argument | Description |
---|---|
date-expression | A date or timestamp expression from which the day of the month value is to be returned. An expression that is the name of a column, the result of another scalar function, or a date or timestamp literal. |
Description
DAYOFMONTH returns the day of the month as an integer from 1 to 31. The date-expression can be 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.TimeStampOpens in a new tab (yyyy-mm-dd hh:mm:ss.fff).
The time portion of the timestamp or $HOROLOG string is not evaluated and can be omitted. The date-expression can also be specified as data type %Library.FilemanDate, %Library.FilemanTimestamp, or %MV.Date.
The DAYOFMONTH and DAY functions are functionally identical.
This function can also be invoked from ObjectScript using the DAYOFMONTH()Opens in a new tab method call:
WRITE $SYSTEM.SQL.DAYOFMONTH("2004-02-25")
Timestamp date-expression
The day (dd) portion of a timestamp string should be an integer in the range from 1 through 31. There is, however, no range checking for user-supplied values. Numbers greater than 31 and fractions are returned as specified. Because (–) is used as a separator character, negative numbers are not supported. Leading zeros are optional on input; leading zeros are suppressed on output.
DAYOFMONTH returns NULL when the day portion is '0', '00', or a nonnumeric value. NULL is also returned if the day portion of the date string is omitted entirely ('yyyy–mm hh:mm:ss'), or if no date expression is supplied.
The elements of a datetime string can be returned using the following SQL scalar functions: YEAR, MONTH, DAYOFMONTH (or DAY), HOUR, MINUTE, SECOND. The same elements can be returned by using the DATEPART or DATENAME function. DATEPART and DATENAME performs value and range checking on day values.
$HOROLOG date-expression
When calculating day of the month for a $HOROLOG value, DAYOFMONTH calculates leap years differences, including century day adjustments: 2000 is a leap year, 1900 and 2100 are not leap years.
DAYOFMONTH can process date-expression values prior to December 31, 1840 as negative integers. This is shown in the following example:
SELECT {fn DAYOFMONTH(-306)} AS DayOfMonthFeb, /* February 29, 1840 */
{fn DAYOFMONTH(-305)} AS DayOfMonthMar /* March 1, 1840 */
The LAST_DAY function returns the date (in $HOROLOG format) of the last day of the month for a specified date.
Examples
The following examples return the number 25 because the date specified is the twenty-fifth day of the month:
SELECT {fn DAYOFMONTH('2004-02-25')} AS DayNumTS,
{fn DAYOFMONTH(59590)} AS DayNumH
The following example also returns the number 25 for the day of the month. The year is omitted, but the separator character (–) serves as a placeholder:
SELECT {fn DAYOFMONTH('-02-25 11:45:32')} AS DayNum
The following examples return <null>:
SELECT{fn DAYOFMONTH('2000-02-00 11:45:32')} AS DayNum
SELECT {fn DAYOFMONTH('2000-02 11:45:32')} AS DayNum
SELECT {fn DAYOFMONTH('11:45:32')} AS DayNum
The following DAYOFMONTH examples all returns the current day of the month:
SELECT {fn DAYOFMONTH({fn NOW()})} AS DoM_Now,
{fn DAYOFMONTH(CURRENT_DATE)} AS DoM_CurrD,
{fn DAYOFMONTH(CURRENT_TIMESTAMP)} AS DoM_CurrTS,
{fn DAYOFMONTH($HOROLOG)} AS DoM_Horolog,
{fn DAYOFMONTH($ZTIMESTAMP)} AS DoM_ZTS
Note that $ZTIMESTAMP returns Coordinated Universal Time (UTC). The other time-expression values return the local time. This may affect the DAYOFMONTH value.
The following example shows that leading zeros are suppressed. It returns a length of either 1 or 2, depending on the day of the month value:
SELECT LENGTH({fn DAYOFMONTH('2004-02-05')}),
LENGTH({fn DAYOFMONTH('2004-02-15')})