AnsweredAssumed Answered

How to add a timezone offset to a date?

Question asked by britta on Jan 10, 2018
Latest reply on Jan 15, 2018 by mmorthala189353

Hi everyone,


I'm running into some timezone problems:

I have a process getting data from a database and inserting into NetSuite.

Our database is in Singapore and time is SST/+8 (Singapore Standard Time), NetSuite uses PT/-8 and Boomi interprets data without a timezone offset as ET/-5.

The database datetime format doesn't come with a timezone offset, so how I can add one, to avoid having all wrong dates in NetSuite?


I've already tried a date function in a map which formats my DB format (yyyyMMdd hh:mm:ss.SSS to a format with timezone yyyy-MM-dd HH:mm:ss.SSS+0800), but the offset ends up showing wrong (-0500 for the first date and -0400 for the second date).

I've also tried setting the format for the first profile as Character and used a function to append '+0800' to the string. The target was formatted as yyyy-MM-dd HH:mm:ss.SSSZ, but I still got the same wrong result as above.


Anyone has any idea how to solve this?


Thank you,



P.S.: In case someone else with a similar problem stumbles across this: In our NetSuite the database uses PT and in the GUI it shows as SST. If you have a field with format 'date', NetSuite will ignore the time and save the date as "midnight UTC", which is 8AM PT (eg. 20180110 080000+0800). If you are in Asia, your GUI will then show local time, which for us would be for the example 20180109 160000-0800. A workaround is to change the setting of the field from 'date' to 'date/time'. That way NetSuite won't "normalize" your data to midnight.