Skip to main content
Solved

Generic Inquiry =day() function not working as expected

  • February 13, 2025
  • 4 replies
  • 159 views
  • Translate All
Varsity I
Forum|alt.badge.img

We were trying to do calculations on orders created each day but the date/time field constrains us.  Parsing out the month/day/year into a new field should have worked, however we are noticing some unusual discrepancies.  It appears anything after 7pm EST is rolling to the next day unprompted.

 

SOOrder.CreateDateTime = 2/12/2025 7:32 PM

=cdate(Concat(CStr(Month([SOOrder.CreatedDateTime])),'/',CStr(Day([SOOrder.CreatedDateTime])),'/',CStr(Year([SOOrder.CreatedDateTime])))) = 2/13/2025

=Day([SOOrder.CreatedDateTime]) = 13

 

SOOrder.CreateDate = 2/12/2025 5:32 PM

=cdate(Concat(CStr(Month([SOOrder.CreatedDateTime])),'/',CStr(Day([SOOrder.CreatedDateTime])),'/',CStr(Year([SOOrder.CreatedDateTime])))) = 2/12/2025

=Day([SOOrder.CreatedDateTime]) = 12

 

 

Best answer by adaughenbaugh95

​@adaughenbaugh95 What you’re seeing is that all dates are actually stored in the database in UTC time, when calling a field into a GI it will adjust for user time zone.

Exceptions are when the field is used in an expression it will force the UTC value.

Users are getting an error trying to filter on the field.

Filter on columfield = @Today (today) or @Today - 1 (yesterday)

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.


I had to reformat my field further.  
=cdate(Concat(CStr(Month(dateadd([SOOrder.CreatedDateTime],'h',-5))),'/',CStr(Day(dateadd([SOOrder.CreatedDateTime],'h',-5))),'/',CStr(Year(dateadd([SOOrder.CreatedDateTime],'h',-5)))))

View original

4 replies

BenjaminCrisman
Acumatica Employee
Forum|alt.badge.img+4

​@adaughenbaugh95 What you’re seeing is that all dates are actually stored in the database in UTC time, when calling a field into a GI it will adjust for user time zone.

Exceptions are when the field is used in an expression it will force the UTC value.

Translate
    Varsity I
    Forum|alt.badge.img

    ​@adaughenbaugh95 What you’re seeing is that all dates are actually stored in the database in UTC time, when calling a field into a GI it will adjust for user time zone.

    Exceptions are when the field is used in an expression it will force the UTC value.

    Thank you for the confirmation.

    I have updated my formula to adjust.

    =cdate(Concat(CStr(Month([SOOrder.CreatedDateTime])),'/',
    CStr(Day(dateadd([SOOrder.CreatedDateTime],'h',-5))),
    '/',CStr(Year([SOOrder.CreatedDateTime]))))

    Translate
      Jr Varsity I
      Forum|alt.badge.img+6

      Hi @adaughenbaugh95

      In addition to ​@BenjaminCrisman comment, all dates are actually stored in the database in UTC time.

      Modify your expression to adjust for EST time zone shift.

      =CDate(DateAdd("h", -5, [SOOrder.CreatedDateTime]))

      I hope, it helps!

      Translate
        Varsity I
        Forum|alt.badge.img

        ​@adaughenbaugh95 What you’re seeing is that all dates are actually stored in the database in UTC time, when calling a field into a GI it will adjust for user time zone.

        Exceptions are when the field is used in an expression it will force the UTC value.

        Users are getting an error trying to filter on the field.

        Filter on columfield = @Today (today) or @Today - 1 (yesterday)

        The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.


        I had to reformat my field further.  
        =cdate(Concat(CStr(Month(dateadd([SOOrder.CreatedDateTime],'h',-5))),'/',CStr(Day(dateadd([SOOrder.CreatedDateTime],'h',-5))),'/',CStr(Year(dateadd([SOOrder.CreatedDateTime],'h',-5)))))

        Translate
          Terms & ConditionsAccessibility 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