I need the date (record's {datecreated}) from saved search to have the timezone offset(eg.-0500).
So until now I found the date pattern that may be used is this one: YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM
So I tested this TO_CHAR(TO_TIMESTAMP_TZ({datecreated}, 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM'), 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM') but the returned hours are not correct (eg. 2023-03-31T00:00:00-04:00). So I tried TO_CHAR({datecreated}, 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM') but this returns ERROR: Invalid Expression. I found that I can get the right time only this way TO_CHAR({datecreated}, 'YYYY-MM-DD"T"HH24:MI:SS') but offset isn't listed.
So my question: has NetSuite a native way to get the correct time with time zone offset or only way is to concatenate the date from different methods? thx
After many hours of trial and error I found that NetSuite have SESSIONTIMEZONE variable and it also have the TZ_OFFSET method. So I concatenated the data from 2 methods to have an "usable" timestamp.
TO_CHAR({datecreated}, 'YYYY-MM-DD"T"HH24:MI:SS') || TZ_OFFSET(SESSIONTIMEZONE)
It will return the exacte date & time with time zone offset. eg.2023-03-31T13:02:07-04:00.
PS. SESSIONTIMEZONE returned values are based on the user preferences.