I have a script that sets a date/time field on our cases for use with our initial response time. We were having some time zone wackiness that I think I have solved and now I am tying to find those that were set wrong.
I feel like I should explain that. When a script (if you use the right parameter) or workflow sets the date/time field it respects the users localization preference. We were unaware that when a user gets their login they have a preference based on our office location (west coast) and so when a user would trigger the script to set the value it would show in the UI as west coast time, even if they were east coast, but the system notes always showed the right time, so I would imagine that the system notes date set field key off of the users system clock.
TL/DR: Date/time fields can have two values, one in the UI and one in the system notes and that gives me something to look for.
I have found how to convert the custom date/time fields for use with other inherent date/time fields fields in NS:
cast({custevent_cxs_init_res_time} as date)
This works a treat.
So, trying to do this with a system notes new value so I can compare the values is giving me nothing but errors.
I have tried:
to_date({systemnotes.newvalue}, 'MM/DD/YYYY HH:MI:SS') to_date({systemnotes.newvalue}) cast({systemnotes.newvalue} as date) And various versions of to_char with combinations of these above. This is my latest version: case when cast({custevent_cxs_init_res_time} as date) <> decode({systemnotes.field}, 'Initial response Time', cast({systemnotes.newvalue} as date)) then 1 else 0 end Any ideas?