MONTHNAME (SQL)

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

Synopsis

{fn MONTHNAME(date-expression)}

Description

MONTHNAME takes as input 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 is not evaluated and can be omitted.

MONTHNAME returns the name of the corresponding calendar month, January through December. The returned value is a character string with a maximum length of 15.

MONTHNAME checks that the date supplied is a valid date. The year must be between 0001 and 9999 (inclusive), the month 01 through 12, and the day appropriate for that month (for example, 02/29 is only valid on leap years). If the date is not valid, MONTHNAME issues an SQLCODE -400 <ILLEGAL VALUE> error.

The names of months default to the full-length American English month names. To change these month name values, use the SET OPTION command with the MONTH_NAME option.

The same month name information can be returned by using the DATENAME function. You can use TO_DATE to retrieve a month name or a month name abbreviation with other date elements. To return an integer corresponding to the month, use MONTH DATEPART or TO_DATE.

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

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

Arguments

date-expression

An expression that evaluates to either an InterSystems IRIS date integer, an ODBC date, or a timestamp. This expression can be the name of a column, the result of another scalar function, or a date or timestamp literal.

Examples

The following examples both return the character string "February" because it is the month of the date expression (February 22, 2018):

SELECT {fn MONTHNAME('2018-02-22')} AS NameOfMonth

SELECT {fn MONTHNAME(64701)} AS NameOfMonth

The following examples all return the current month:

SELECT {fn MONTHNAME({fn NOW()})} AS MnameNow,
       {fn MONTHNAME(CURRENT_DATE)} AS MNameCurrDate,
       {fn MONTHNAME(CURRENT_TIMESTAMP)} AS MNameCurrTS,
       {fn MONTHNAME($HOROLOG)} AS MNameHorolog,
       {fn MONTHNAME($ZTIMESTAMP)} AS MNameZTS

The following example shows how MONTHNAME responds to an invalid date (the year 2017 was not a leap year):

SELECT {fn MONTHNAME("2017-02-29")}

The SQLCODE -400 error code is issued with the %msg indicating <ILLEGAL VALUE>.