MONTH

Synopsis

MONTH(date-expression)
{fn MONTH(date-expression)}

Description

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 tab (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 tab method call:

$SYSTEM.SQL.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('2000-02-16') AS Month_Given

SELECT {fn MONTH(59589)} 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 returns zero because the month is omitted:

SELECT MONTH('2000--16') AS Month_Given

SELECT {fn MONTH('12:34:55')} AS Month_Given

SELECT MONTH('2000 12:34:55') AS Month_Given

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

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

• SQL functions: DATEPART, DATENAME, DAYOFMONTH, LAST_DAY, MONTHNAME, TO_DATE

• ObjectScript function: $ZDATE

• ObjectScript special variables: $HOROLOG, $ZTIMESTAMP