Issue with Time Display in Email Velocity Script

[Anonymous]

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!

[SanfordWhiteman]

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):

[Anonymous]

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]

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]

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

[SanfordWhiteman]

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]

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]

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]

Sorry, can you explain what you mean by that?

[SanfordWhiteman]

Add those lines to the script, post the output.