Velocity script token in friendly date format

[MarketingMe]

I have a velocity script token in my email to add 30 days from today's date with a "friendly" date format: "Feb 25, 2021"

I also directly include a Date field using its {{lead.token}}: {{lead.Membership End Date:default=end date}}. However, that output is of course not in a "friendly" date format, but in the default system format "2021-01-22".

Sample email copy: 

Your membership expired on 2022-01-22; however, if you renew before Feb 25, 2021 we'll be happy to extend your renewal rate.

I know I need to use Velocity to output the Membership End Date instead of using the {{lead.token}} directly but how do I format that field?

[SanfordWhiteman · Accepted solution]

Assuming your Membership End Date field's Velocity name is $lead.MembershipEndDate (you'll know the real name when you check the field name off in the tree in Script Editor, it's not something we can 100% predict):

## standard Velocity date/time includes
#set( $defaultTimeZone = $date.getTimeZone().getTimeZone("America/New_York") )
#set( $defaultLocale = $date.getLocale() )
#set( $calNow = $date.getCalendar() )
#set( $ret = $calNow.setTimeZone($defaultTimeZone) )
#set( $calConst = $field.in($calNow) )
#set( $ISO8601DateOnly = "yyyy-MM-dd" )
#set( $ISO8601DateTime = "yyyy-MM-dd'T'HH:mm:ss" )
#set( $ISO8601DateTimeWithSpace = "yyyy-MM-dd HH:mm:ss" )
#set( $ISO8601DateTimeWithMillisUTC = "yyyy-MM-dd'T'HH:mm:ss.SSSZ" )
## your business-specific friendly date format
#set( $FORMAT_FRIENDLY_DATE_ONLY = "MMM dd, yyyy" )
## cast date-like String to Date
#set( $MembershipEndDate = $convert.parseDate(
    $lead.MembershipEndDate,
    $ISO8601DateOnly, 
    $defaultLocale, 
    $defaultTimeZone 
) )
## output in format
${date.format($FORMAT_FRIENDLY_DATE_ONLY,$MembershipEndDate,$defaultLocale,$defaultTimeZone)}

[Kevin_Vaughn]

Hi Sanford,

Thank you for providing this script it is really helpful! I am having one issue though that maybe you can shed some light on.

I am using the scrip almost identically as you have posted it, the only difference being that I am displaying and formatting the time as well. The issue that I'm having is that 2 hours are being added to the time when output from script token. When I view the data in Marketo I see the correct date/time originally submitted from my form, but after I format the date within my email the time has two hours added to it every time.

## standard Velocity date/time includes
#set( $defaultTimeZone = $date.getTimeZone().getTimeZone("America/New_York") )
#set( $defaultLocale = $date.getLocale() )
#set( $calNow = $date.getCalendar() )
#set( $ret = $calNow.setTimeZone($defaultTimeZone) )
#set( $calConst = $field.in($calNow) )
#set( $ISO8601DateOnly = "yyyy-MM-dd" )
#set( $ISO8601DateTime = "yyyy-MM-dd'T'HH:mm:ss" )
#set( $ISO8601DateTimeWithSpace = "yyyy-MM-dd HH:mm:ss" )
#set( $ISO8601DateTimeWithMillisUTC = "yyyy-MM-dd'T'HH:mm:ss.SSSZ" )
## your business-specific friendly date format
#set( $FORMAT_FRIENDLY_DATE_ONLY = "MMMM dd, yyyy h':'mm a" )
## cast date-like String to Date
#set( $requestMeetingDate = $convert.parseDate(
    $member.requestMeetingDate,
    $ISO8601DateTime, 
    $defaultLocale, 
    $defaultTimeZone 
) )
## output in format
${date.format($FORMAT_FRIENDLY_DATE_ONLY,$requestMeetingDate,$defaultLocale,$defaultTimeZone)}

Kevin

[SanfordWhiteman]

Can you print the raw output of

 $member.requestMeetingDate

?

[Kevin_Vaughn]

Here is the raw output, 2021-03-18T10:30:00.000-05:00, which formats to March 18, 2021 10:30 AM.

And here is  a view of the person in Marketo with the two hour time difference.

[SanfordWhiteman]

The primary problem is you aren't parsing the timezone with your format, just ignoring it. And you don't want to pass a TimeZone object to the $convert.parseDate method if the time zone is in the string itself.

But the wider problem is the string

2021-03-18T10:30:00.000-05:00

can't be parsed using the standard SimpleDateFormat symbols. If you simply add the symbol Z, that won't match because of the extra colon in the offset.  Z expects a string like this:

2021-03-18T10:30:00.000-0500

so you need to massage the string a bit first, like this:

## standard Velocity date/time includes
#set( $defaultTimeZone = $date.getTimeZone().getTimeZone("America/New_York") )
#set( $defaultLocale = $date.getLocale() )
#set( $calNow = $date.getCalendar() )
#set( $ret = $calNow.setTimeZone($defaultTimeZone) )
#set( $calConst = $field.in($calNow) )
#set( $ISO8601DateOnly = "yyyy-MM-dd" )
#set( $ISO8601DateTime = "yyyy-MM-dd'T'HH:mm:ss" )
#set( $ISO8601DateTimeWithSpace = "yyyy-MM-dd HH:mm:ss" )
#set( $ISO8601DateTimeWithMillisTZ= "yyyy-MM-dd'T'HH:mm:ss.SSSZ" )
## your business-specific friendly date format
#set( $FORMAT_FRIENDLY = "MMMM dd, yyyy h':'mm a" )
## adjust one part of date-like string to use supported Z format
#set( $member.requestMeetingDate = $member.requestMeetingDate.replaceAll(":(?=\d{2}$)","" ) )
## cast date-like String to Date
#set( $requestMeetingDate = $convert.parseDate(
    $member.requestMeetingDate,
    $ISO8601DateTimeWithMillisTZ, 
    $defaultLocale
) )
${date.format( $FORMAT_FRIENDLY, $requestMeetingDate, $defaultLocale ,$defaultTimeZone)}

[Kevin_Vaughn]

Thank you for taking the time to provide this (your check's in the mail 😉). I am pretty sure I would not have figured this out on my own, at least not by my deadline. Very grateful for your assistance.