We have a custom date time field. Since we are a global organization, we would like to display a date according to the time zone preference as well as a specific time zone in a saved search. Outputting the plain date field in the saved search takes care of the local time zone display. For the specific time zone, (IST), I was attempting to use a conversion formula. It works for dates set when the date was set with time zone preference was EST. When the date is set in any other time zone, the converted date/time is incorrect.
The first screenshot shows the saved search as displayed with the time zone preference set to EST. The first line shows a record that set the date in the EST time zone. The second record shows the date when set in the IST time zone. The Date to QC field displays as expected. The IST Date to QC shows correctly for the date that was set in EST. However, the date that was set in IST shows an incorrect converted date. As a control, I output the date as a string with no conversion. It appears that the date / time is stamped according to the time zone preference. The second screenshot shows the same saved search when the time zone preference is set to IST.
Is there a way to extract the time zone the date was set in so that I can apply the correct conversion formula? The formula I’m using is:
to_char((from_tz(to_timestamp(to_char({custrecord_date_to_qc}, 'YYYY-MM-DD HH:MI:SS PM'), 'YYYY-MM-DD HH:MI:SS PM') ,'AMERICA/NEW_YORK') at time zone 'Asia/Calcutta'),'MM/DD/YY HH:MI:SS PM TZD')
If I could determine the time zone the date was set, then I could change the ‘AMERICA/NEW_YORK’ time zone to the appropriate time zone.