1. SQL Reference Guide
  2. Built-in Functions
  3. Functions on Timestamps
  4. to_last_day_of_month function

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 12-23 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"}