RE: Using OpenQuery, how to change UTC Date/Time to Local Time

Answered

Given the following query, how do I convert the CREATE_DATE, which is UTC, to local time?

SELECT *
FROM OPENQUERY([LINKED_NETSUITE_DB], ‘
SELECT TOP 1 CREATE_DATE CreateDateUTC, ”HowToConvertToLocalTime?” CreateDateLocalTime
FROM ODBC.ENTITY
‘);

rbigej Rookie Asked on September 28, 2020 in SuiteAnalytics Connect.
Add Comment
1 Answers
Best answer

You can do the conversion locally.  What database are you using?  If SQL Server, the following may work:

SELECT CREATE_DATE AS CreateDateUTC,
DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), GETDATE()), CREATE_DATE) AS CreateDateLocalTime
FROM OPENQUERY([LINKED_NS_PROD], 'SELECT TOP 1 CREATE_DATE FROM ODBC.ENTITY');

Make sure that the date you are getting back really is in UTC, otherwise this conversion will not produce the correct output.

Beginner Answered on September 28, 2020.

Thank you, that will work for many of my use cases!

There are times (for performance), where I want to do it within the OPENQUERY statement.  Any thoughts on how to do it?  Is there a DATEADD equivalent within OpenQuery?

on September 28, 2020.

Well, the first thing you’d need to do is find a consistent way to get the date/time in UTC from an ODBC query.  I have not found any functions that work to do this yet, so I am afraid I can’t offer a way to do it remotely.  If you did find one, then adding or subtracting hours in Oracle is easy, as you simply add or subtract (# of hours / 24) to a date/time value, for example, adding five hours would just be CREATE_DATE + (5/24)

Generally, though, I would not think performing calculations remotely would improve performance.  NetSuite is a multi-tenant system with unpredictable load and demands on it, while you likely have much more power at your disposal on the local system to perform conversions.   The best approach is usually a targeted query to get just the columns and rows of data that you need via OpenQuery, then do further processing if necessary locally.

on September 28, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.