NetSuite date format in Celigo export -- where set?

Comments

3 comments

  • Tyler Lamparter Principal Product Manager
    Awesome Follow-up
    Engaged
    Top Contributor
    Answer Pro
    Celigo University Level 4: Legendary

    Richard Brown, it will use the date format AND timezone associated with the user who authenticated with NetSuite when setting up the connection in Celigo or who created the tokens manually. This is just how NetSuite handles everything for apis, saved searches, suitescripts, and so on. What I would do is create a formula text field on your saved searches instead of the actual field. Then within that formula text field, have to_char({now},'YYYY-MM-DD'). Replace the now with the date field you want to use. In the case of datetime fields, I would do to_char({now},'YYYY-MM-DD HH24:MI:SS'). Now if you also want to convert timezone to GMT, you could do that in your saved search or with handlebars in IO. If in the saved search, you could do something like this: to_char(new_time({now}, ‘CST’, ‘GMT’),‘YYYY-MM-DD HH24:MI:SS’). Assuming you're NetSuite instance was in CST and you were converting to GMT.

     

    You could do these conversions in Celigo with handlebars, but I've found that if someone else reauthenticates the NetSuite connection and they have different preferences, then it could negatively impact your data flow by switching to their preference.

    0
  • Richard Brown

    Thanks Tyler...your explanation makes sense and I appreciate the very detailed examples...including NetSuite examples. It's nice that your example output format YYYY-MM-DD HH24:MI:SS is easily converted into a datetime column in SQL!

    I'm glad I asked -- it would be very annoying if someone had to recreate the Connector and inadvertently changed the export date format! :(((((

    0
  • Tyler Lamparter Principal Product Manager
    Awesome Follow-up
    Engaged
    Top Contributor
    Answer Pro
    Celigo University Level 4: Legendary

    Richard Brown no problem! I've dealt with this issue in the past and this was my sure-fire way to indefinitely solve it. 

    0

Please sign in to leave a comment.