Issue with Time Display in Email Velocity Script

Anonymous
Not applicable

Issue with Time Display in Email Velocity Script

Launched Marketo in Nov-16 and during implementation a couple of MyTokens were set-up using Velocity script to convert dates and times, to our local timezone Australia/Sydney:

  • {{my.Appt Date}}   - for the clients appointment date with consultant
  • {{my.Appt Date Time}}    - for appointment time with consultant (the actual field it references also includes the date)

The date token is working fine, but the time token is displaying 7 hours later than it should be, (e.g. 5pm instead of 10am). The date and time are pulled from separate Fields in the database.

This is the script that's being used:

#set( $inTimeZone = $date.getTimeZone().getTimeZone('Australia/Sydney') )

#set( $outTimeZone = $date.getTimeZone().getTimeZone('Australia/Sydney') )

#set( $locale = $date.getLocale() )

#set( $myDate = $convert.parseDate(${lead.apptBookedTime},'yy-MM-dd HH:mm',$locale,$inTimeZone) )

${date.format('hh:mm a',$myDate,$locale,$outTimeZone)}

I don't have much experience with Velocity, did a bit of reading online, but can't work out why the above would be showing the time incorrectly.

Can anyone help me solve? Thanks in advance!

25 REPLIES 25
SanfordWhiteman
Level 10 - Community Moderator

Re: Issue with Time Display in Email Velocity Script

Is the field apptBookedTime truly storing the datetime in your local tz, or is it storing it in UTC?

What do you see if you output

${lead.apptBookedTime}

And what do you mean by

The date and time are pulled from separate Fields in the database.

I only see one Lead (Person) field in your code, ​apptBookedTime.

Also, please highlight code using the Advanced Editor's syntax highlighter (you can select Java for Velocity):

pastedImage_2.png

Anonymous
Not applicable

Re: Issue with Time Display in Email Velocity Script

apptBookedTime is stored in local time zone

${lead.apptBookedTime} is also showing 5pm

Please ignore the separate fields comment.

I'm not sure where to access the advanced editor in order to highlight - is this something I need to download?

SanfordWhiteman
Level 10 - Community Moderator

Re: Issue with Time Display in Email Velocity Script

I need to know the full, exact string format. Assuming it's not just the 3 characters "5pm".

For the editor, you don't need to download anything, you have to find the link for Advanced Editor to the upper right of the regular editor.

Anonymous
Not applicable

Re: Issue with Time Display in Email Velocity Script

This is the format:

Date Time :

YYYY-MM-DDThh:mm:ss+((11or10) AEST time zone from GMT)

Eg: 2015-01-06T19:15:29+11:00

2016-06-21T13:20:18+10:00

There's no option for advanced editor - see screenshot

Capture.PNG

SanfordWhiteman
Level 10 - Community Moderator

Re: Issue with Time Display in Email Velocity Script

YYYY-MM-DDThh:mm:ss+((11or10) AEST time zone from GMT)

Eg: 2015-01-06T19:15:29+11:00

2016-06-21T13:20:18+10:00

OK, then that doesn't match the date format you're telling Velocity to expect.

You want:

#set( $myDate = $convert.parseDate($lead.apptBookedTime,"yyyy-MM-dd'T'HH:mm",$locale,$inTimeZone) )

The format you pass to parseDate() must be a subset of the actual string format ("subset" meaning you can leave off some placeholders at the end, as I did with the seconds and the time zone, but it otherwise has to match the value). Otherwise parsing will fail.

There's no option for advanced editor - see screenshot

The Advanced Editor is on this site (the Nation site). It's how you provide highlighted, monospaced code in a post.

Anonymous
Not applicable

Re: Issue with Time Display in Email Velocity Script

That makes sense, I updated it. However now the time is 12:00 AM, when it should be 10:00 AM, (before it was 5:00 PM)

Here's the script as it is now:

#set( $inTimeZone = $date.getTimeZone().getTimeZone('Australia/Sydney') )

#set( $outTimeZone = $date.getTimeZone().getTimeZone('Australia/Sydney') )

#set( $locale = $date.getLocale() )

#set( $myDate = $convert.parseDate(${lead.apptBookedTime},"yyyy-MM-dd'T'HH:mm",$locale,$inTimeZone) )

${date.format('hh:mm a',$myDate,$locale,$outTimeZone)}

SanfordWhiteman
Level 10 - Community Moderator

Re: Issue with Time Display in Email Velocity Script

Please show the full output with these debugging lines at the end:

${myDate}

${lead.apptBookedTime}

Reading and writing in the same time zone should not cause any skew.

Anonymous
Not applicable

Re: Issue with Time Display in Email Velocity Script

Sorry, can you explain what you mean by that?

SanfordWhiteman
Level 10 - Community Moderator

Re: Issue with Time Display in Email Velocity Script

Add those lines to the script, post the output.