Generated column- date of the last day of the month

General
1

I’m trying to create a generated column that will produce the date of the last day of the month based on a reference to the created date column.

I tried the Last_Day(created date) function. This works to generate a date in the generated column, but the date is wrong. For example, Last_Day(08/27/2021 11:15:16) produces an output of 08/30/2021 17:00:00. There are 31 days in August, so clearly something is off. Also I don’t know why the time changes.

Ultimately I am trying to generate columns with dates that I can use to then filter financial data that meet before and after conditions based on the generated dates.

2

Hello @James_Greff

Welcome to our community and thank you for trying out Backendless.

The last_day function returns the date of the last day in Unix Timestamp format = 1629802800000, which corresponds to 08/31/2021 00:00:00 (UTC + 00: 00 Coordinated Universal Time Etc/GMT)
You can check it using REST CONSOLE
You are in UTC-07: 00 timezone, so the console adjusts the date 08/31/2021 00:00:00 to match your timezone = 08/31/2021 00:00:00 - 07:00 = 08/30/2021 17:00:00

The result in what format do you want to get:

  1. 31
    Type: STRING
    Expression: DAYOFMONTH(LAST_DAY(datetimeColumn))
  2. 2021-08-31
    Type: STRING
    Expression: LAST_DAY(datetimeColumn)
    …
    ?
3

Thanks for your response. This solved my issue. I also discovered the Date(datetimecolumn) function to be useful which converts a datetime into an INT with a yyyymmdd format. I don’t need the time, and I find the INT format easy to work with. The Last_Day function also works for dates that have been converted to INT using the date() function.