Solved

Date Shift in Generic Inquiry Parameter


I have a Generic Inquiry that sums transactions over a date range specified by Required Parameters StartDate and ThruDate with Default Values of @YearStart and @MonthEnd-1 respectively:

Generic Inquiry Parameters

As I needed the ThruDate in Excel exports of this GI, I added it to the Results Grid:

Generic Inquiry Results Grid

The unexpected result is the Parameter (Thru Date) and the Result column (Statement Date) get different values when the Inquiry is executed:

Results

If the Parameter is manually changed in the results screen, the dates match.

The value in the resultant column is the date being applied (not the defaulted date displaying at top) verified by the calculated values and manually entering the dates.

 

icon

Best answer by lauraj46 7 June 2021, 23:43

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

View original

5 replies

Userlevel 3
Badge +1

Seems like it did @MonthEnd+1 rather than @MonthEnd-1.

What about replacing
@MonthEnd-1
with
DateAdd(@MonthEnd,’d’,-1)
?

Thanks @TimRodman,

I am using the @MonthEnd to as a default for a parameter in the GI, so the DateAdd() is not available to me there, or I am naïve of it’s availability.  As I need to allow user to be able override the default value, defaulting the parameter is preferred.

 I was delighted that the @MonthEnd-1 was giving me the last date of the prior month, as desired, in the Parameters section.  When seeing some unexpected results, I output the parameter value to the grid and found it to be different (the next day) from the value in the parameters.

I wrote a GI just to test this and posted those results to:
https://www.augforums.com/forums/postid/8892/ .

Showed same “bug” in @WeekEnd, @MonthEnd, @PeriodEnd, @QuarterEnd, @YearEnd without any adjusting math.

I have not tested same in Report Designer, harvesting the parameters as suggested in https://www.augforums.com/forums/postid/8865/.

Userlevel 2
Badge

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

Userlevel 3
Badge +1

@brianmcmillin100 Ah, good point, that won’t work in a Generic Inquiry parameter. I was thinking of Report Designer parameters which allow you to write formulas.

 

Seems like @lauraj46 has this one figured out. Maybe Acumatica just needs to introduce separate “UTC” options for the date relative options like they have in the expression editor:

 

Userlevel 2
Badge

Agree with @TimRodman that UTC options on  Date Relative options would be helpful!  

It would be great to have the ability to build an arbitrary formula for parameter defaults like you can in the result fields and the conditions.  With that capability you could write the formula to make an adjustment for the UTC.

Laura

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2020  Acumatica, Inc. All rights reserved