function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
WrogWrogWrogWrog 

Text conversion of Now() is not consistent with value

I've been trying to make our mass emails more "personal" by including an intelligent "good morning", "good Afternoon", or "good evening" before the recipient's name. I have a timezone field on the contact to enter the hours before/after GMT, so that by calculating the recipient's current time at the point of sending, I can set the greeting:

if(value(MID (TEXT (now()), 12, 2))+Time_Zone__c>24,"Good morning ",if(value(MID (TEXT (now()), 12, 2))+Time_Zone__c>17,"Good evening ",if(value(MID (TEXT (now()), 12, 2))+Time_Zone__c>12,"Good afternoon ",if(value(MID (TEXT (now()), 12, 2))+Time_Zone__c>00,"Good morning ","Good evening "))))

All works fine in theory, and for the most part in practice, but if I send email near midday, I find that some emails go out as "good morning", when it is patently after 12.

 

After some exploration, I find that the TEXT function is at fault. If the value of  NOW()28/09/2009 14:02, then the value of TEXT(NOW())2009-09-28 13:02:18Z. ie the GMT time, rather than the local timeHence the inconsistency in my calculation.

 

I would apply some kind of 1 hour correction, but how do I know when each country switches on/off daylight saving time? My only other alternative is to remember never to send emails near 12:00 or 24:00 (or 13:00 or 01:00) (this may be good practice anyway, but that's not my point!!)

 

I guess Salesforce can come up with a justification for this behaviour, but I would like to suggest it is a bug which should be fixed

 

Unless someone else can help me.......

mpiercempierce

To be clear, what you're storing is not a time zone but an offset. A time zone is something like America/Los_Angeles -- sometimes it means UTC-8, sometimes it means UTC-7. Timezones and offsets are not synonymous.

 

Why not just parse the datetime into UTC, apply a timezone (which will yield an offset), then do calculations on that local time? That should be more reliable.

 

Also, while we're being precise about time, GMT is not the same as UTC. The short version is that GMT tracks the variable rotation speed of Earth, while UTC tracks seconds at a constant rate regardless of whether the Earth happens to be spinning slower or faster this year. So, when you see a datetime whose timezone is "Z", that actually means UTC, not GMT. (Z is short for Zulu time, a nickname for UTC.)

WrogWrogWrogWrog
Even if I had known all that, I would still have been frustrated that now() is a different string of characters to text(now()), an inconsistency which wasted me more hours than I would have spent researching the difference between GMT and Zulu time!