Thursday, October 18, 2018

Formula to Convert Date Field into a Specific Time Zone

User created a sales order search and would like to display date field (e.g. Date Created) in the result in a different time zone (e.g. Pacific to Eastern). This is possible through formula. See sample search below:

1. Navigate to Lists > Search > Saved Searches > New.

2. Select Transaction.

3. Provide a Title.

4. In the Criteria tab > Standard subtab, set the filters.

5. In the Results tab > Columns subtab, add fields to display.

6. Add Formula (Text).

7. In the Formula field, use: FROM_TZ(CAST(TO_DATE(To_Char({datecreated}, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP), 'PST') AT TIME ZONE 'EST'

8. Click Save & Run.

Note: Enhancement#87743 has been filed for the ability to run a search or report and get the data in the Time zone specified.

No comments:

Post a Comment