6 Replies Latest reply on Mar 7, 2017 11:44 PM by Sanford Whiteman

    Updating datetime field type with velocity script

      I am looking to pull a datetime field type, {paymentStartDateTime_c}, add one year to the value and display the result.

       

      I also need the value displayed to adjust based on the leads time zone, {timeZone_c}.

       

      Here is the code I am using:

       

      This is the output for a lead located in US/Central time:

          

           $z.add(1, 1) $z.add(10,2) $date.format('medium',$z)

       

      Suggestions?

       

      Thank you!

          • Re: Updating datetime field type with velocity script
            Sanford Whiteman

            That seems... long.

             

            #set( $locale = $date.getLocale() )
            #set( $inTimeZone = $date.getTimeZone().getTimeZone($lead.timeZone__c) )
            #set( $outTimeZone = $date.getTimeZone().getTimeZone($lead.timeZone__c) )## in and out in same tz
            #set( $inCalendar = $convert.toCalendar($convert.parseDate($lead.paymentStartDateTime__c, 'yyyy-MM-dd HH:mm:ss', $locale, $inTimeZone)) )
            #set( $tmp = $inCalendar.add( $field.in($inCalendar).YEAR, 1) )
            ${date.format('medium', $inCalendar, $locale, $outTimeZone)}
            
              • Re: Updating datetime field type with velocity script
                Ryan Huber

                Sanford Whiteman How easy is it to edit what you've posted to add an hour to a datetime field?

                 

                Basically we have a datetime field that when I try to format it for legible use in an email, Marketo chops an hour off of it (because of that weird Velocity timezone thing they've got going on). I just need to add the hour back in within my script token.

                 

                I've successfully done this in the past with days, but can't get hours to work. Any help would be appreciated!

                  • Re: Updating datetime field type with velocity script
                    Sanford Whiteman

                    Check my answer on https://nation.marketo.com/message/155985

                     

                    You shouldn't use date arithmetic for timezones: tz support is all built-in with Date objects.

                      • Re: Updating datetime field type with velocity script
                        Ryan Huber

                        Hmm. The code on that page doesn't want to work.

                         

                        It's not really a timezone issue... all of my datetime fields are hard coded, so timezone is really irrelevant. For whatever reason, Marketo is just stripping an hour off of it and I need to add it back in.

                         

                        Last time I tried something like this, the following worked:

                         

                        #set($x = $date.calendar)

                        $x.add(5,36)

                        $date.format('EEEE, MMMM dd, yyyy',$x)

                         

                        When i try to substitute a Marketo field in for $date.calendar and change (5,36) to (10,1), it returns "$date.format('EEEE, MMMM dd, yyyy',$x)" in the email.

                         

                        Any thoughts?

                          • Re: Updating datetime field type with velocity script
                            Sanford Whiteman

                            It's always a timezone issue. It's not like their server time just lost a random hour!

                             

                            This is predictable (though at times frustrating) behavior when Java Dates move in and out of String format without being closely managed.

                             

                            Realize there are three equally important ingredients in Date management:

                             

                            • storing Dates: Date objects are typically stored as epoch time in milliseconds (epoch time is UTC/GMT/Zulu/Z). This is the definition of a moment in time.
                            • parsing Dates from strings: unless you instruct otherwise, strings are parsed as if they are in the Velocity timezone, that is, pod timezone, before being converted to UTC.
                            • formatting Dates to strings: unless you instruct otherwise, Dates are output in the pod timezone.

                             

                            Your datetime custom fields being "hard coded" (?) doesn't make a difference... they're still output into the Velocity environment as static Strings (not Dates), formatted in your instance (not pod) timezone.  To work with them in any way, even just to reformat them, you have to parse them into real, living Date objects. But unless you specifically tell Velocity otherwise (by using the overloaded parsing methods to read the date in its original tz) it has no possible way to know these strings aren't from the current pod timezone. That's how you get shifts of hours (and a full calendar day in the worst cases) when outputting in Velocity.