Mail Merge and GMT/UTC Dates

dev98 · #1 05-05-2011, 05:45 AM

Hello All,

I have the following issue and I could not find anything about it on the web.
I have a Date Time field in my DB and it is always stored in GMT/UTC time (for example 17.06.2011 03:59:59)

When I do the mail merge to that field, it displays the date and time as is in the DB. What I actually want is to convert it to the machine's local time zone. Is that possible with Mail Merge?

Thanks very much in advance for any help

macropod · #2 05-05-2011, 04:30 PM

Hi Dev98,

This is possible, but the field coding to do so is complex:

Code:

{QUOTE{SET StartDate {MERGEFIELD UTCDate}}
{ASK Delay "What is the local UTC offset, in decimal hours (eg 2.5 or -11.5)?" \o}
{SET SumTime{=({StartDate \@ HH}+{Delay})*3600+{StartDate \@ mm}*60+{StartDate \@ ss}}}
{SET OffsetDay {=INT(SumTime/86400)-(SumTime<0)}}
{SET OffsetTime {=(SumTime<0)*86400+SumTime}}
{SET Hrs {=MOD(INT(OffsetTime/3600)+23,24)+1}}
{SET Min {=INT((OffsetTime-Hrs*3600)/60)}}
{SET Sec {=MOD(OffsetTime,60)}}
{SET a{=INT((14-{StartDate \@ M})/12)}}
{SET b{={StartDate \@ yyyy}+4800-a}}
{SET c{={StartDate \@ M}+12*a-3}}
{SET d{StartDate \@ d}}
{SET jd{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045+OffsetDay}}
{SET e{=INT((4*(jd+32044)+3)/146097)}}
{SET f{=jd+32044-INT(146097*e/4)}}
{SET g{=INT((4*f+3)/1461)}}
{SET h{=f-INT(1461*g/4)}}
{SET i{=INT((5*h+2)/153)}}
{SET dd{=h-INT((153*i+2)/5)+1}}
{SET mm{=i+3-12*INT(i/10)}}
{SET yy{=100*e+g-4800+INT(i/10)}}
{QUOTE "{dd}-{mm}-{yy} {Hrs}:{Min}:{Sec}" \@ "dddd, d MMMM yyyy' at 'HH:mm:ss"}}

Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste them from this message.

The above field code:
• assumes your date/time mergefield's name is 'UTCDate';
• prompts the user for the GMT/UTC offset; and
• outputs the adjusted date/time in "dddd, d MMMM yyyy at 'HH:mm:ss" format.

Change the mergefield's name to match your own. If you want to hard code the UTC/GMT offset, change the line:
{ASK Delay "What is the local UTC offset, in decimal hours (eg 2.5 or -11.5)?" \o}
to:
{SET Delay 5}
where the '5' represents the offset. Similarly, you can edit the adjusted date/time format to suit your needs.

Rather than trying to convert all the above into real fields manually, there's a macro I developed to do it that you can use, at: http://www.gmayor.com/export_field.htm#TextToField

dev98 · #3 05-06-2011, 05:02 AM

Thanks very much for the detailed reply. Unfortunately in my case it's not an option to ask the customer to enter their offset so I figured out a slightly different way - After I get the data from the DB in my application I modify it to adjust the time zone prior to passing it to the mail merging engine in my application and thus I mail merge the modified data. But I think this post will serve many people as there was nothing written on this topic. It will be great though if mail merge can figure out the date offset from the PC settings so that the user will not have to enter it.
Thanks again for the valuable information

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Saving INDV mail merges During the mail merge	sedain121	Mail Merge	2	10-04-2011 07:52 PM
mail merge	ericlch16	Mail Merge	3	04-30-2011 04:44 AM
Mail Merge	nonworduser	Mail Merge	0	08-24-2010 02:28 PM
help with a mail merge	akim281	Mail Merge	2	02-05-2010 02:54 PM
Mail Merge	Mohanss82	Mail Merge	0	01-21-2009 12:29 PM