Design

Back Next

lastDay

Returns the date of the last day of the month for each date in a column.

Syntax

date:lastDay(date)

The following table describes the argument for this command:

Argument	Required/ Optional	Description
date	Required	Date/Time data type. Passes the date for which you want to return the last day of the month. You can enter any valid transformation expression that evaluates to a date.

Return Value

Date. The last day of the month for the date value that you pass to this function.

NULL if a value in the selected column is NULL.

Example

The following expression returns the current date as the last day:

date:lastDay(fn:current-dateTime('DATE'))

The following table lists some sample values and return values:

DATE	RETURN VALUE
18-04-98 01:00	Apr 18 1998 01:00:00 AM
20-08-99 05:00	Aug 20 1999 05:00:00 AM

The following expression returns the last day of the previous month for each date in the DATE column:

date:lastDay(date:addToDate(fn:current-dateTime('DATE','MM',-1))

The following table lists some sample values and return values:

DATE	RETURN VALUE
Apr 1 1998 12:00:00AM	Mar 31 1998 12:00:00AM
Jan 6 1998 12:00:00AM	Dec 31 1997 12:00:00AM
Feb 2 1996 12:00:00AM	Jan 31 1996 12:00:00AM
NULL	NULL

You can nest toDate to convert string values to a date. toDate function always includes time information. If you pass a string that does not have a time value, the date returned will include the time 00:00:00.

The following example returns the last day of the month for each date in the same format as the string:

date:lastDay(toDate('DATE', 'MON-DD-YYYY'))

The following table lists some sample values and return values:

DATE	RETURN VALUE
'18-NOV-98'	Nov-30-1998 00:00:00
'28-APR-98'	Apr-30-1998 00:00:00
NULL	NULL
'18-FEB-96'	Feb-29-1996 00:00:00 (Leap year)

date:lastDay(date:toDate("DATE", "YYYY-MM-DD"))

The following table lists some sample values and return values:

DATE	RETURN VALUE
'18-NOV-98'	1998-Nov-30 00:00:00
'28-APR-98'	1998-Apr-30 00:00:00
NULL	NULL
'18-FEB-96'	1996-Feb-29 00:00:00 (Leap year)

Date and time functions

Download Guide

Watch

Comments

Cloud Application Integration Homepage

0 COMMENTS

We’d like to hear from you! Log in to comment.

Rename Saved Search

Table of Contents

Design

Design

lastDay

lastDay

Syntax

Return Value

Example