How to query dates using "between" parameter ?

[#1]

Hello Experts,

I need to set validation for the below condition.

Condition : If user try to enter a From Date/ To Date between the existing From date and To date, it should through error.

Here in the example image the second record is invalid as its From Date falls between First Row's From and To.

I tried the below code:

	var queryPP = CBO_PayPeriod.QueryByElements;
	var queryPP_SelParams = queryPP.CreateSelectionParams();

	queryPP_SelParams.Add(queryPP.FromDate, "I", "BT", this.FromDate, this.ToDate );//, this.ToDate);
	queryPP_SelParams.Add(queryPP.ToDate, "I", "BT", this.FromDate, this.ToDate );//, this.ToDate);

	var queryPP_Results = queryPP.ExecuteFromDBDataOnly(queryPP_SelParams);


	if (queryPP_Results.Count() > 1)
	{
		raise Msg.Create("E", "Payperiod already available for the selected from and to date");
		return false;
	}

But my query doesn't return any record, so the validation fails. Could anyone point what could be issue ?

Thanks,

Senz

[former_member665936]

Hi senz.evo,

It looks like there is a logic issue in your query to match the condition above.

In the parameters, you are looking for a "from date" that MUST be between the "from date" and "to date" of your record and the same logic applies to the "to date".

For the pay period "JAN2020D1", the first query parameter will find the record, as the "from date" is between the two dates in "JAN2020". However, the "to date" of the second record is not between the two dates of the first one.

If I correctly understood the condition that should be tested, my guess is to keep only the first parameter "from date", that should be between the dates of your first record.

That should resolve your problem!

[former_member187312]

Hi matheus.wurth,

Sorry, I missed a detail in my question. I have two cases in my query, below image would explain better.

Here I'm expecting the query should consider both the cases.

One way I could think of is like first check only From date that falls between an existing record's date and throw error then clear parameter and repeat the same for To Date.

	var queryPP = CBO_PayPeriod.QueryByElements;
	var queryPP_SelParams = queryPP.CreateSelectionParams();

	queryPP_SelParams.Add(queryPP.FromDate,"I", "BT", this.FromDate, this.ToDate );

	var queryPP_Results = queryPP.ExecuteFromDBDataOnly(queryPP_SelParams);

        if(queryPP_Results.Count()> 0)
        {
           raise Msg.Create("E", "Payperiod already available for the selected from and to date");
           return false;
        }

        queryPP_SelParams.Clear();

	queryPP_SelParams.Add(queryPP.ToDate,"I", "BT", this.FromDate, this.ToDate );

	var queryPP_Results = queryPP.ExecuteFromDBDataOnly(queryPP_SelParams);

        if(queryPP_Results.Count()> 0)
        {
           raise Msg.Create("E", "Payperiod already available for the selected from and to date");
           return false;
        }

But its not an optimized one. Anyways to combine both the logic into single query execution?

BR,

Senz