Using OpenQuery, how to change UTC Date/Time to Local Time
Given the following query, how do I convert the CREATE_DATE, which is UTC, to local time?
FROM OPENQUERY([LINKED_NETSUITE_DB], ‘
SELECT TOP 1 CREATE_DATE CreateDateUTC, ”HowToConvertToLocalTime?” CreateDateLocalTime
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.