Compare Dates (without time) in BQL

Question

I have a custom table in Acumatica which has a datetime column. In this column I am also preserving the time. I then have another custom inquiry screen on this table and on this screen there is a date-range filter.

The issue I have is that in the filter, the user will only enter the dates (not the time). Is there a clean way to perform the query on the database without taking into consideration the time?

For example if I have a row with the datetime set as 01-06-2020 14:00. and in the date range filter, the user has selected up to 01-06-2020, then this row will not be returned (when I use LessEqual), but I want it returned. I can of course do some work on the filtering side, perhaps programmatically changing the filter date to include the date such as 01-06-2020 23:59, but I would prefer if I just do the query based on dates without time. In SQL it is possible as per the following example: Best way to compare dates without time in SQL Server

I am not sure whether you can somehow do something similar in Acumatica.

I guess the easiest way will be to keep a separate field storing only Date and set Filter on that field. — Samvel Petrosov, Jun 01 '20 at 23:45
Thanks Samvel, so you mean that we need to store that field in the database, right? because I don't think an unbound field will work in this case - since it will not be possible to use an unbound field in the PXSelect database query. — Joseph Caruana, Jun 02 '20 at 04:16
yes, I don't think it will work with unbohnd fields as they are only on DAC level. — Samvel Petrosov, Jun 02 '20 at 04:18

score 0 · Accepted Answer · answered Jun 20 '20 at 03:58

The approach we ended up using was to modify the filter by including additional 2 fields so that they tweak the from/to dates accordingly before sending the query to the db:

#region FromDateQuery
[PXDate(InputMask = "g", DisplayMask = "g")]
public virtual DateTime? FromDateQuery
{
    get
    {
        return (FromDate.HasValue ? FromDate.Value : ToDate);
    }
}
public abstract class fromDateQuery : PX.Data.BQL.BqlDateTime.Field<fromDateQuery> { }
#endregion
#region ToDateQuery
[PXDate(InputMask = "g", DisplayMask = "g")]
public virtual DateTime? ToDateQuery
{
    get
    {
        return (ToDate.HasValue ? ToDate.Value.Date.AddDays(1).AddSeconds(-1) : ToDate);
    }
}
public abstract class toDateQuery : PX.Data.BQL.BqlDateTime.Field<toDateQuery> { }
#endregion

Compare Dates (without time) in BQL

1 Answers1