Skip to main content
Answer

How can I filter a report on a date/time filter and adjust for UTC

  • November 26, 2024
  • 4 replies
  • 197 views
B
Freshman II
Forum|alt.badge.img

I want to filter a report based on the CreatedDateTime and adjust for UTC.  I have the filter working properly in the body of the report but how can I use it in the filter of the report overall?

Here’s what I want:

=dateadd([EPActivityApprove.CreatedDateTime],'h',-6) = Today()

or 

as another option, how can I filter so [EPActivityApprove.CreatedDateTime] = Today(UTC)

Best answer by noorula77

Hi ​@bduprey19,
     try with below approaches:

  1. For the filter, you can use:
    [EPActivityApprove.CreatedDateTime] >= DateAdd('h',6,Today()) AND 
    [EPActivityApprove.CreatedDateTime] < DateAdd('h',6,DateAdd('d',1,Today()))
  1. Alternatively, you could create a calculated field in your report's schema:
    =DateAdd('h',-6,[EPActivityApprove.CreatedDateTime])
  1. For filtering based on UTC directly, you can use:
    [EPActivityApprove.CreatedDateTime] >= Today() AND 
    [EPActivityApprove.CreatedDateTime] < DateAdd('d',1,Today())

 

    4 replies

    M
    Jr Varsity II
    Forum|alt.badge.img+1
    • mbridges00
    • Jr Varsity II
    • November 27, 2024

    There are 2 options - NowUTC() and TodayUTC() - available on reports that should use UTC time. I’ve never used them, so I can’t verify they work, but they should be available to select for both fields on the report and the report filters.

     

     

    Chris Hackett
    N
    Jr Varsity III
    Forum|alt.badge.img+1
    • noorula77
    • Jr Varsity III
    • Answer
    • November 27, 2024

    Hi ​@bduprey19,
         try with below approaches:

    1. For the filter, you can use:
      [EPActivityApprove.CreatedDateTime] >= DateAdd('h',6,Today()) AND 
      [EPActivityApprove.CreatedDateTime] < DateAdd('h',6,DateAdd('d',1,Today()))
    1. Alternatively, you could create a calculated field in your report's schema:
      =DateAdd('h',-6,[EPActivityApprove.CreatedDateTime])
    1. For filtering based on UTC directly, you can use:
      [EPActivityApprove.CreatedDateTime] >= Today() AND 
      [EPActivityApprove.CreatedDateTime] < DateAdd('d',1,Today())

     

    Chris Hackett
    darylbowman
    Captain II
    Forum|alt.badge.img+15

    I'm guessing you're asking based on experiencing a need; however, I was fairly certain that Acumatica adjusts the returned CreatedDateTime based on the user's time zone. It's not stored that way, but I think it should be returned that way if the user's time zone / server time zone is correct.

    Daryl⚡Freelance Acumatica Developer (MVPx2🏆) for hire ⏩ linkedin.com/in/darylbowman
    Chris Hackett
    B
    Freshman II
    Forum|alt.badge.img
    • bduprey19
    • Author
    • Freshman II
    • November 27, 2024

    ​@darylbowman I’m not sure if using an API changes how the CreateDateTime stamp works, but the example is when I sync my time via an API @ 11/26/2024 5:30a central time, the CreateDateTime stamp is 11/25/2024 11:30p (6 hours earlier).

    darylbowman
    Terms & ConditionsCookie settingsAccessibility statement
    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 — 2024  Acumatica, Inc. All rights reserved