NetSuite date format in Celigo export -- where set?

Richard Brown

• January 10, 2023 02:11

Hello All -- I am exporting project records from NetSuite via Saved Search flow. The date fields are exported in European D/M/YYYY format. My personal date format setting is DD-MMM-YYYY. The NetSuite Company Preferences setting is the European format. Does the export via Celigo always use the Company setting? This is hinted by this Celigo support post. I need to be sure of the setting to apply the correct conversions for the destination. Thanks!

0

• 

  Tyler Lamparter Principal Product Manager

  • January 10, 2023 04:41

  

  

  

  

  

  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

  • January 10, 2023 23:59

  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

  • January 11, 2023 00:08

  

  

  

  

  

  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.