Convert Dynamics GP DEX_ROW_TS date value to local time using SQL

By Steve Endow

Back in 2014, the venerable Tim Wappat wrote an excellent blog post on the DEX_ROW_TS field that exists in some Dynamics GP tables.

http://www.timwappat.info/post/2014/12/04/Beware-DEX_ROW_TS-for-data-synchronisation

Occasionally, I have needed to check the TS field to see when a record was inserted or updated.  The downside is that in doing so, I am usually frustrated by the fact the timestamp is recorded in UTC.

There are a metric ton of pitfalls related to date and time conversion, with time zones and daylight saving time being the most common, but I finally took the time to lookup a basic SQL function that can be used to convert the UTC time value into the approximate time of the SQL Server.

Please note that there are likely many potential technical issues with this approach, so you shouldn't assume the resulting local time is 100% correct for all dates and times, but I suspect in most cases, it will be sufficient for the typical situation where you are trying to research an issue in GP.  If you need perfectly accurate time stamps for reporting purposes, you'll need something more comprehensive, like a .NET based solution.

Here is the post from StackOverflow where the solution was posted, and you can read the many comments and arguments about how different approaches have various limitations.

With all of those disclaimers, here's an example of the quick and dirty function:

SELECT DEX_ROW_TS, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), DEX_ROW_TS) AS LocalTime
FROM GL10000


Enjoy!

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com