Last Day of Month in Optic Query

Question

Is there any function available in Optic API to identify last day of a month?

I have requirement to identify a date whether its last day of a month or not.

Answer 1

I suggest adding one day to the date/dateTime, and then seeing if the month has changed using sql.month(), ie:

op.fromView('main', 'expenses')
  .bindAs('nextDay', op.sql.timestampadd('SQL_TSI_DAY', 1, op.col("submitted")))
  .where(op.ne(op.sql.month(op.col("submitted")),op.sql.month(op.col("nextDay")))
  .result();