Chapter 48 SQL function DAYOFMONTH

Date function that returns the month date of a date expression.

outline

{fn DAYOFMONTH(date-expression)}

parameter

• Date expression - the date or timestamp expression from which to return the month value date. An expression that is a column name, the result of another scalar function, or a date or timestamp literal.

describe

DAYOFMONTH returns the day ordinal of a month as an integer between 1 and 31. A date expression can be a date integer, $HOROLOG, or $ZTIMESTAMP value, a date string in ODBC format, or a timestamp.

Date expression timestamp can be data type% library Positime (encoded 64 bit signed integer) or data type% library TimeStamp(yyyy-mm-dd hh: mm: ss.fff).

The time of $TIMESTAMP or the part of $TIMESTAMP cannot be omitted.

DAYOFMONTH and DAY functions are functionally identical.

You can also call this function from ObjectScript using the DAYOFMONTH() method call:

DHC-APP>  w $SYSTEM.SQL.DAYOFMONTH("2018-02-25")
25

Timestamp date-expression

The day (Dd) part of the timestamp string should be an integer between 1 and 31. However, a range check is not performed on user supplied values. Returns numbers and scores greater than 31 in the specified manner. Negative numbers are not supported because (-) is used as a separator. Leading zeros on the input are optional; Leading zeros are not displayed on the output.

DAYOFMONTH returns NULL when the date part is' 0 ',' 00 ', or a non numeric value. NULL is also returned if the date part of the date string ('yyyy mm HH: mm: ss') is completely omitted, or if no date expression is provided.

You can use the following SQL scalar functions to return elements of a date time string: year, month, dayofmonth (or day), hour, minute, second. Use the DATEPART or DATENAME functions to return the same elements. DATEPART and DATENAME perform value and range checks on date values.

$HOROLOG date expression

In calculating the month date of the $HOROLOG value, DAYOFMONTH calculates the leap year difference, including the century day adjustment: 2000 is a leap year, 1900 and 2100 are not leap years.

DAYOFMONTH can treat the date expression value before December 31, 1840 as a negative integer. The following example shows this:

SELECT {fn DAYOFMONTH(-306)} AS DayOfMonthFeb,    /* February 29, 1840 */
       {fn DAYOFMONTH(-305)} AS DayOfMonthMar,    /* March 1, 1840     */
       {fn DAYOFMONTH(-127410)} AS DayOfMonthFeb  /* February 29, 1492 */
       
29	1	29

LAST_ The day function returns the date (in $HOROLOG format) of the last day of the month of the specified date.

Examples

The following example returns the number 25 because the specified date is the 25th day of the month:

SELECT {fn DAYOFMONTH('2018-02-25')} AS DayNumTS,
       {fn DAYOFMONTH(64704)} AS DayNumH

25	25

The following example also returns the number 25, which represents a day of the month. Omit the year, but use the separator (-) as a placeholder:

SELECT {fn DAYOFMONTH('-02-25 11:45:32')} AS DayNum

25

The following example returns < null >:

SELECT{fn DAYOFMONTH('2018-02-00 11:45:32')} AS DayNum

SELECT {fn DAYOFMONTH('2018-02 11:45:32')} AS DayNum

SELECT {fn DAYOFMONTH('11:45:32')} AS DayNum

The following DAYOFMONTH examples return the current date of the current month:

SELECT {fn DAYOFMONTH({fn NOW()})} AS DoM_Now,
       {fn DAYOFMONTH(CURRENT_DATE)} AS DoM_CurrD,
       {fn DAYOFMONTH(CURRENT_TIMESTAMP)} AS DoM_CurrTS,
       {fn DAYOFMONTH($HOROLOG)} AS DoM_Horolog,
       {fn DAYOFMONTH($ZTIMESTAMP)} AS DoM_ZTS
       
8	8	8	8	8

Note that $ZTIMESTAMP returns coordinated universal time (UTC). Other time expression values return local time. This may affect the DAYOFMONTH value.

The following example shows that leading zeros are cancelled. It returns a value of length 1 or 2, depending on the date of the month value:

SELECT LENGTH({fn DAYOFMONTH('2018-02-05')}),
       LENGTH({fn DAYOFMONTH('2018-02-15')})


1	2