Skip to main content
Answer

Generic Inquiry =day() function not working as expected

  • February 13, 2025
  • 4 replies
  • 94 views
A
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)))))

4 replies

BenjaminCrisman
Acumatica Employee
Forum|alt.badge.img+4
  • BenjaminCrisman
  • Acumatica Support Team
  • February 13, 2025

​@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.

Chris Hackett
A
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]))))

BenjaminCrisman
A
Jr Varsity I
Forum|alt.badge.img+5
  • Ankita Tayana
  • Jr Varsity I
  • February 13, 2025

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!

Chris Hackett
A
Varsity I
Forum|alt.badge.img
  • adaughenbaugh95
  • Author
  • Varsity I
  • Answer
  • February 13, 2025

​@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)))))

V
Chris Hackett
saifalisabri
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