Skip to main content

MONTH (SQL)

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

Synopsis

MONTH(date-expression)

{fn MONTH(date-expression)}

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 tab (an encoded 64-bit signed integer), or data type %Library.TimeStampOpens in a new tab (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 tab 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.

Arguments

date-expression

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 both return the number 2 because February is the second month of the year:

SELECT MONTH('2018-02-22') AS Month_Given
SELECT {fn MONTH(64701)} AS Month_Given

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

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

See Also

FeedbackOpens in a new tab