to_last_day_of_month function

The to_last_day_of_month function returns the last day of the month for a given timestamp.

Syntax:

TIMESTAMP to_last_day_of_month(<timestamp>)

Semantics:

• timestamp: The timestamp argument takes a TIMESTAMP value or a value that can be cast to a TIMESTAMP type

• Return Value: TIMESTAMP(0) The function return NULL in the following cases:

  • If the timestamp argument is NULL.

  • If the input timestamp is not castable to TIMESTAMP type.

Example 1 - For a subscriber, print the last day of the month in which the account subscription expires.

SELECT sa.acct_id, to_last_day_of_month(sa.account_expiry) AS lastday
FROM stream_acct sa
WHERE profile_name="DM"

Output:

{"acct_id":4,"lastday":"2024-03-31T00:00:00Z"}