Table of Contents

Search

  1. Preface
  2. The Transformation Language
  3. Constants
  4. Operators
  5. Variables
  6. Dates
  7. Functions
  8. Creating Custom Functions
  9. Custom Function API Reference

Transformation Language Reference

Transformation Language Reference

LAST_DAY

LAST_DAY

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

Syntax

LAST_DAY(
date
)
The following table describes the argument for this command:
Argument
Required/
Optional
Description
date
Required
Date/Time datatype. Passes the dates 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 that date value you pass to this function.
NULL if a value in the selected port is NULL.

Null

If a value is NULL, LAST_DAY ignores the row. However, if all values passed from the port are NULL, LAST_DAY returns NULL.

Group By

LAST_DAY groups values based on group by ports you define in the transformation, returning one result for each group. If there is no group by port, LAST_DAY treats all rows as one group, returning one value.

Examples

The following expression returns the last day of the month for each date in the ORDER_DATE port:
LAST_DAY( ORDER_DATE )
ORDER_DATE
RETURN VALUE
Apr 1 1998 12:00:00AM
Apr 30 1998 12:00:00AM
Jan 6 1998 12:00:00AM
Jan 31 1998 12:00:00AM
Feb 2 1996 12:00:00AM
Feb 29 1996 12:00:00AM   
(Leap year)
NULL
NULL
Jul 31 1998 12:00:00AM
Jul 31 1998 12:00:00AM
You can nest TO_DATE to convert string values to a date. TO_DATE 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 order date in the same format as the string:
LAST_DAY( TO_DATE( ORDER_DATE, 'DD-MON-YY' ))
ORDER_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)

0 COMMENTS

We’d like to hear from you!