Skip to main content

DAYOFMONTH (SQL)

A date function that returns the day of the month for a date expression.

Synopsis

{fn DAYOFMONTH(date-expression)}

Description

DAYOFMONTH returns the day of the month as an integer from 1 to 31. The date-expression can be 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 or $HOROLOG string is not evaluated and can be omitted.

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.Functions.DAYOFMONTH("2018-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     */
       {fn DAYOFMONTH(-127410)} AS DayOfMonthFeb  /* February 29, 1492 */

The LAST_DAY function returns the date (in $HOROLOG format) of the last day of the month for a specified date.

Arguments

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.

Examples

The following examples return the number 25 because the date specified is the twenty-fifth day of the month:

SELECT {fn DAYOFMONTH('2018-02-25')} AS DayNumTS,
       {fn DAYOFMONTH(64704)} 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('2018-02-00 11:45:32')} AS DayNum
SELECT {fn DAYOFMONTH('2018-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('2018-02-05')}),
       LENGTH({fn DAYOFMONTH('2018-02-15')})

See Also

FeedbackOpens in a new tab