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.

[Anonymous]

I think this is what you mean?

#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)}
${myDate}  
${lead.apptBookedTime}

If not I may need to get a developer here to help me!

[SanfordWhiteman]

I need to see the output generated by this code.

Output = what the tokens renders as in an email.

This is just showing me your source code, and it doesn't show me anything specific to your case.

You were showing me output above.

[Anonymous]

Ok, I just quoted what my IT team told me was the format. I'm not technical so hence issues with understanding terminology.

I'm going to leave this here, as more complicated than I thought and will engage a developer to sort out with my IT team. Thanks for help so far.

[SanfordWhiteman]

Ok, I just quoted what my IT team told me was the format. I'm not technical so hence issues with understanding terminology.

But, you see, this doesn't make sense.

You must know the format, since all you need to do is output ${lead.apptBookedTime} from a one-line Velocity script and it's right there for you to see.

If the format changes across different leads, that's pretty bad, and would only be possible if the field is a Marketo String field.

A Marketo Datetime field will always have the same format in Velocity; it isn't possible for it to look different from lead to lead.

A String field can hold any value, including both various date-like values and and other string value. (Like the string "David Chase".)

What type of field is this in Admin >> Field Management?

I'm going to leave this here, as more complicated than I thought and will engage a developer to sort out with my IT team. Thanks for help so far.

Thing is, you're not going to find anyone who knows Velocity better than Nicho and me (it is a very niche skill).

So you don't need to go anywhere else, you need to tell us the actual, predictable input (input = the field value) so we can tell you how to output it.

[Anonymous]

Hi Sanford and Nicho,

I am assisting David in resolving this issue. Thanks so much for your help so far.

To answer your questions above:

The 'Appt Booked Time' field is a Date/time field.

INPUT:

SCRIPT:

#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)}

OUTPUT:

Our Marketo Location Settings:

Let me know if you require anything else,

- Suzanne

[Anonymous]

In addition to the above, if I simply input ${lead.apptBookedTime}

Output is:

[SanfordWhiteman]

Appointment time: 2018-02-12 23:00:00

OK, so we're back to the format

"yyyy-MM-dd HH:mm:ss"

You can't parse a string as

"yyyy-MM-dd'T'HH:mm"

If it doesn't actually have the T character!

In addition, if it's coming out as 11:00am the day before, it's clear that this data is being output in something other than Australia/Sydney (your pod time and instance time are not the same). It looks like US Central time, the common pod time.

So you want:

#set( $inTimeZone = $date.getTimeZone().getTimeZone('America/Chicago') )
#set( $outTimeZone = $date.getTimeZone().getTimeZone('America/Sydney') )
#set( $locale = $date.getLocale() )
#set( $myDate = $convert.parseDate(${lead.apptBookedTime},"yyyy-MM-dd HH:mm",$locale,$inTimeZone) )
${date.format('hh:mm a',$myDate,$locale,$outTimeZone)} 

[Anonymous]

I see what you mean. It looks like that worked .

Amazing, thanks so much for your help!

[SanfordWhiteman]

Sure, glad it's working.

[Kelci_Martinsen]

This entire thread was super helpful for troubleshooting an issue I've been having (in addition to your blog post --> https://blog.teknkl.com/velocity-days-and-weeks/#notes) Thank you so much!

[SanfordWhiteman]

I need to see the raw value of ${lead.apptBookedTime} for this same lead, in the same context (simply add that line to the end of the script, after a <br> to make it readable).

Also please use the syntax highlighter when pasting code.