Skip to main content

This is documentation for Caché & Ensemble.

For information on converting to InterSystems IRISOpens in a new window, see the InterSystems IRIS Migration Guide and Migrating to InterSystems IRIS, both available on the WRC Distributions pageOpens in a new window (login required).


A date function that returns the month as an integer for a date expression.


{fn MONTH(date-expression)}


Argument Description
date-expression An expression that is the name of a column, the result of another scalar function, or a date or timestamp literal.


MONTH returns an integer specifying the month. The month integer 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.TimeStampOpens in a new window (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.

The month (mm) portion of a date string should be an integer in the range from 1 through 12. There is, however, no range checking for user-supplied values. Numbers greater than 12, zero, 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 and trailing zeros are suppressed on output.

MONTH returns zero when the month portion is '0', '00', or a nonnumeric value. Zero is also returned if the month portion of the date string is omitted entirely ('yyyy––dd'), or if no date expression is supplied.

MONTH interprets the second numeric string encountered in a date string as the month value, so omitting the year portion of the date string ('mm-dd hh:mm:ss'), results in the second number encountered ('dd') being treated as the month value. Thus, a leading hyphen or some placeholder should be supplied for an unknown year value; for compatibility with Caché, 9999 is generally the preferred default year value.

Note that MONTH can be invoked as an ODBC scalar function (with the curly brace syntax) or as an SQL general function.

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


The elements of a datetime string can be returned using the following SQL functions: YEAR, MONTH, DAY (or DAYOFMONTH), HOUR, MINUTE, and SECOND. The same elements can be returned by using the DATEPART or DATENAME function. Date elements can be returned using TO_DATE. DATEPART and DATENAME performs value and range checking on month values.

The LAST_DAY function returns the date of the last day of the specified month.


The following examples both return the number 2 because February is the second month of the year:

SELECT MONTH('2000-02-16') AS Month_Given
Copy code to clipboard
SELECT {fn MONTH(59589)} AS Month_Given
Copy code to clipboard

The following example sorts records in birthday order by month and day, ignoring the year component of the DOB:

SELECT Name,DOB AS Birthdays
FROM Sample.Person
Copy code to clipboard

The following examples returns zero because the month is omitted:

SELECT MONTH('2000--16') AS Month_Given
Copy code to clipboard
SELECT {fn MONTH('12:34:55')} AS Month_Given
Copy code to clipboard
SELECT MONTH('2000 12:34:55') AS Month_Given
Copy code to clipboard

The following example returns the number 2 because a placeholder character (-) has been supplied for the omitted year:

SELECT {fn MONTH('-02-16')} AS Month_Given
Copy code to clipboard

The following examples all return the current month:

SELECT {fn MONTH({fn NOW()})} AS MNow,
       MONTH($HOROLOG) AS MHorolog,
Copy code to clipboard

See Also

FeedbackOpens in a new window