Date Shift in Generic Inquiry Parameter

Hi @brianmcmillin100 ,

I think that UTC, not the date subtraction, is your issue.  I tested this with @MonthEnd and see the same behavior.  I’ve seen similar behavior on other Generic Inquiries.  

According to the Acumatica documentation:

All the date-relative parameters use the date (in UTC) of the server used to run the Acumatica ERP instance as the current date.                                                                

Running a SQL Trace on the query you may see something like this:

DECLARE @P0 nvarchar(MAX) SET @P0='6/30/2021 11:59:59 PM' 
DECLARE @P1 SmallDateTime(4) SET @P1='7/1/2021 3:59:59 AM' 

SELECT TOP (15) [PMProject].[ContractID] AS [PMProject_ContractID], @P0 AS [ThruDate]
FROM [Contract] [PMProject]
WHERE ... AND ( [PMProject].[ExpireDate] <= @P1)

Notice that P0 and P1 are different, where P0 is the result column and P1 is the value used in the WHERE clause.

On the other hand, when the user enters a manual date for the parameter, the UTC conversion does not happen.  As a result, the system uses the date as specified.

Another point to bear in mind is that the UTC adjustment will only cause a visible date change for the date-relative “end”dates.  This is because the relative start date parameters are of the form ‘6/1/2021 00:00:00 AM’, therefore adding some number of hours to this doesn’t shift the date.

Maybe you could make your ThruDate default to @MonthStart (instead of @MonthEnd - 1), and set your condition to be < [ThruDate].  If you do this then users would need to aware that when they enter an end date manually the results will only include records BEFORE that date.

I would love to get some further clarification from other Acumatica GI wizards (if some are monitoring this conversation) to confirm that I am understanding this correctly and maybe there’s a better way to do this!

Laura