Solved

Adjusting CreatedDateTime, Time for correct region

  • 23 February 2023
  • 4 replies
  • 274 views
D
Rank
Userlevel 4
Badge
  • Del
  • Freshman I
  • 39 replies

I have created a Generic Inquiry that will show the Date and Time that a Sales Order was printed.

I am using the SOOrderShipment.CreatedDateTime field and the following code to extract the time.

=IIF(HOUR([SOOrderShipment.CreatedDateTime])<=12,PADLEFT(CSTR(HOUR([SOOrderShipment.CreatedDateTime])),2,'0')+':'+PADLEFT(CSTR(MINUTE([SOOrderShipment.CreatedDateTime])),2,'0')+ ' AM',PADLEFT(CSTR(HOUR([SOOrderShipment.CreatedDateTime])-12),2,'0')+':'+PADLEFT(CSTR(MINUTE([SOOrderShipment.CreatedDateTime])),2,'0')+ ' PM')

This works as it should other than it displays the Time as UTC time. I need it to display the time for our region. We are in the US Eastern time zone (-5 hours). 

Thank you for any assistance.

 

icon

Best answer by kdavis45 23 February 2023, 17:21

View original

4 replies

BenjaminCrisman
Rank
Userlevel 7
Badge +4

Hi @Del! It is Microsoft standard to store all date/time fields in UTC time, it is only when the field is viewed in the UI that the user profile time zone is applied to the date seen.

The issue is that when you use a date time field in an expression, it will always pull the time stored in SQL and not apply the UI time zone, so you would need to apply a -5 to the hours, but if someone is in a time zone of -4 or -6, then they will still see the -5 hrs EST time.

D
Rank
Userlevel 4
Badge

BenjaminCrisman

Thank you for the quick response. I understand that the time is stored as UTC, and am trying to find a way to convert the UTC time to our local time while using the code that extracts the time for the CreatedDateTime field.

Also all our users are located in the US Eastern Time Zone.  

kdavis45
Rank
Userlevel 4
Badge +1

I’ve historically used the dateadd formula with the interval of ‘h’ (hours) and add of -5 (from UTC to ET).

 

It would look something like this: =dateadd([SOOrderShipment.CreatedDateTime], ‘h’, -5).

Kristen Davis, Accordant Company LLC
BenjaminCrisman
Rank
Userlevel 7
Badge +4

@Del I was just typing the option which @kdavis45 suggested, this should work as needed for this.

Also though, you could try just adding -5 to the Hour() function part, seems like it should pick that up correctly.

Reply


Terms & ConditionsCookie settings
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