[SOLVED] How to fetch start date and end date of a month in BIRT Report based on the report parameter

How to fetch start date and end date of a month in BIRT Report based on the report parameter

We have 2 report parameters start date and end date. If user enters start date then the end date should be calculated based on the start date and vice versa. For example

startdate = 2024-03-01 00:00:00.000 then end date should be 2024-03-31 23:59:59.998

I tried something like below however it doesn't work, In the below case user enters end date and the start date should be calculated automatically based on end date.

if(params["startdate"].value == null && params["enddate"].value)
{
endDate = MXReportSqlFormat.getEndDayTimestampFunction(params["endDate"])
startDate = new Date(endDate.getFullYear(), endDate.getMonth(), 1)
where  += " and a.actualdate >= " + startDate
}

Solution

Your approach is error-prone.

You are constructing SQL satements (as it seems) by concatening SQL text fragments. This is usually the opposite of best practice (risk of SQL inection).

Usually, your database should provide date arithmetic. For example, in Oracle there is the LAST_DAY function.

And of course, your query should use bind variables unless there are very good reasons to not use them.