How to make SuiteQL TO_CHAR on a datetime field return UTC time

AI generated answer (using internal Slack threads):

To make SuiteQL TO_CHAR on a datetime field return UTC time, you can use the

TO_TIMESTAMP_TZ

function to convert the datetime field to a timestamp with a timezone, and then use

TO_CHAR

to format it. Here is an example:

formulatext:TO_CHAR(TO_TIMESTAMP_TZ(TO_CHAR({lastmodifieddate},'YYYY/MM/DD HH24:MI:SS'), 'YYYY/MM/DD HH24:MI:SS TZH'),  'YYYY/MM/DD HH24:MI:SS TZH:TZM')

This will convert the

lastmodifieddate

field to a timestamp with a timezone, and then format it to a string in the 'YYYY/MM/DD HH24MISS TZH:TZM' format. Another approach is to use the

CAST

function to cast the datetime field to a timestamp with a local timezone, and then use

TO_CHAR

to format it. Here is an example:

formulatext:TO_CHAR(CAST({lastmodifieddate} AS TIMESTAMP WITH LOCAL TIME ZONE), 'YYYY-MM-DD\\\"T\\\"HH24:MI:SS')||TZ_OFFSET(SESSIONTIMEZONE)

This will cast the

lastmodifieddate

field to a timestamp with a local timezone, and then format it to a string in the 'YYYY-MM-DD"T"HH24MISS' format. The

TZ_OFFSET(SESSIONTIMEZONE)

function is used to append the timezone offset to the formatted string. ****** Disclaimer: This response was generated by an artificial intelligence assistant based on previous conversations from multiple Slack channels, and may contain inaccuracies or mistakes. ******