MONTHNAME
Synopsis
{fn MONTHNAME(date-expression)}
Arguments
date-expression | An expression that evaluates to either a Caché 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. |
Description
MONTHNAME takes as input 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.
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 1841 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 error (Fatal error occurred).
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.MONTHNAME(date-expression)
Examples
The following examples both return the character string "February" because it is the month of the date expression (February 16, 2000):
SELECT {fn MONTHNAME('2000-02-16')} AS NameOfMonth
SELECT {fn MONTHNAME(59589)} 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 Embedded SQL example shows how MONTHNAME responds to an invalid date (the year 2001 was not a leap year):
SET testdate="2001-02-29"
&sql(SELECT {fn MONTHNAME(:testdate)} INTO :a)
IF SQLCODE'=0 {
WRITE !,"Error code ",SQLCODE,!
WRITE %msg,! }
ELSE {
WRITE !,"returns: ",a }
QUIT
The SQLCODE -400 error code is issued with the %msg indicating <ILLEGAL VALUE>. This validity test is performed before testing if the date is in the allowed range of years (after 1840).
The following embedded SQL example shows how MONTHNAME responds to a valid, but out-of-range date (the year 1835 is too early for Caché SQL):
SET testdate="1835-02-19"
&sql(SELECT {fn MONTHNAME(:testdate)} INTO :a)
IF SQLCODE'=0 {
WRITE !,"Error code ",SQLCODE,!
WRITE %msg,! }
ELSE {
WRITE !,"returns: ",a }
QUIT
The SQLCODE -400 error code is issued with the %msg indicating <VALUE OUT OF RANGE>.
See Also
-
SQL functions: DATEPART DATENAME DAYOFMONTH MONTH TO_DATE
-
ObjectScript function: $ZDATE
-
ObjectScript special variables: $HOROLOG $ZTIMESTAMP