Skip to main content
Previous sectionNext section

MONTH

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

Synopsis

MONTH(date-expression)

{fn MONTH(date-expression)}

Arguments

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.

Description

MONTH returns an integer specifying the month. The month integer 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 window (an encoded 64-bit signed integer), or data type %Library.TimeStampOpens in a new window (yyyy-mm-dd hh:mm:ss.fff).

The month (mm) portion of a date string must be an integer in the range 1 through 12. Leading zeros are optional on input. Leading and trailing zeros are suppressed on output.

The date portion of date-expression is validated and must include a month within the range 1 through 12 and a valid day value for the specified month and year. Otherwise, an SQLCODE -400 error <ILLEGAL VALUE> is generated.

The time portion of date-expression is not validated and can be omitted.

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:

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

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.

Examples

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

SELECT MONTH('2018-02-22') AS Month_Given
Copy code to clipboard
SELECT {fn MONTH(64701)} 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
ORDER BY MONTH(DOB),DAY(DOB),Name
Copy code to clipboard

The following examples all return the current month:

SELECT {fn MONTH({fn NOW()})} AS MNow,
       MONTH(CURRENT_DATE) AS MCurrD,
       {fn MONTH(CURRENT_TIMESTAMP)} AS MCurrTS,
       MONTH($HOROLOG) AS MHorolog,
       {fn MONTH($ZTIMESTAMP)} AS MZTS
Copy code to clipboard

See Also

FeedbackOpens in a new window