Custom expression to calculate number of days in a given month.

Custom expression to calculate number of days in a given month.

If there is a requirement to calculate the number of days in a given month, the following expression will provide this and it does not depend on local client regional settings.

Here is an example custom expression to calculate the number of days in a month:

DateDiff("day",
    DateAdd("day",(-DayOfMonth([MyDateColumn])) + 1,Date([MyDateColumn])),
    DateAdd("month",1,DateAdd("day",(-DayOfMonth([MyDateColumn])) + 1,Date([MyDateColumn])))
)

where [MyDateColumn] is your column with a DataType of Date or DateTime for which you want to calculate how many days are in that month.

Additionally, this expression used included in the above expression is useful for finding the first day of a given month:

DateAdd("day",(-DayOfMonth([MyDateColumn])) + 1,Date([MyDateColumn]))

Doc: TIBCO Spotfire Professional User's Guide > Date and Time Functions

• https://docs.tibco.com/pub/sfire-analyst/7.11.0/doc/html/en-US/TIB_sfire-analyst_UsersGuide/index.htm