4 Replies Latest reply on Oct 2, 2017 7:52 AM by Kelly Toomey

    Pull data from table based on date - velocity Script

    Kelly Toomey

      Hi All

      We have a table of student enrollments where they will have multiple enrollments added in one day. I would like to send one email a day with all of their enrollments from the previous day.  Right now I have a script that pulls all the enrollments on their record. I just can't seem to get it to pull off of a specified date.

       

      My thought is to have today's date set and then have it look for today's date - 1 so it always pulled the previous day. I've tried a bunch of variations but i'm at a loss!

       

      This is what I am working with to pull all of the enrollments:

       

      #set( $count = 0 )

      <p>User details:</p>

      <table>

          #foreach( $coce_enrollmentid in $coce_enrollmentList )

              <tr>

        

                  <td> Enroll ID ${coce_enrollmentList.get($count).coce_enrollmentid} |</td>

                  <td> Name ${coce_enrollmentList.get($count).coce_name} | </td>

                  <td>Created On ${coce_enrollmentList.get($count).createdon} | </td>

                <td> Enroll Date ${coce_enrollmentList.get($count).coce_enrolldate} | </td>

                <td> Status ${coce_enrollmentList.get($count).statecode} | </td>

                <td> Status Reason ${coce_enrollmentList.get($count).statuscode} | </td>

              </tr>

              #set( $count = $count + 1 )

          #end

      </table>

        • Re: Pull data from table based on date - velocity Script
          Sanford Whiteman

          To get yesterday's date object in a timezone-ignorant way:

           

          #set( $Calendar = $date.getCalendar() )
          #set( $tmp = $Calendar.add($field.in($Calendar).DATE, -1) )
          

           

          However, you should search and read all my Velocity-related posts on datetimes and timezones. Converting to the appropriate timezone is critical to get this right instead of usually-right.

          1 of 1 people found this helpful
            • Re: Pull data from table based on date - velocity Script
              Kelly Toomey

              Seems to work but I get a crazy long string with day time hour minute etc. Do I need to set a format?

               

              java.util.GregorianCalendar[time=1505923664329,areFieldsSet=true,areAllFieldsSet=true,lenient=true,zone=sun.util.calendar.ZoneInfo[id="CST6CDT",offset=-21600000,dstSavings=3600000,useDaylight=true,transitions=149,lastRule=java.util.SimpleTimeZone[id=CST6CDT,offset=-21600000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=3,startMonth=2,startDay=8,startDayOfWeek=1,startTime=7200000,startTimeMode=0,endMode=3,endMonth=10,endDay=1,endDayOfWeek=1,endTime=7200000,endTimeMode=0]],firstDayOfWeek=1,minimalDaysInFirstWeek=1,ERA=1,YEAR=2017,MONTH=8,WEEK_OF_YEAR=38,WEEK_OF_MONTH=4,DAY_OF_MONTH=20,DAY_OF_YEAR=263,DAY_OF_WEEK=4,DAY_OF_WEEK_IN_MONTH=3,AM_PM=0,HOUR=11,HOUR_OF_DAY=11,MINUTE=7,SECOND=44,MILLISECOND=329,ZONE_OFFSET=-21600000,DST_OFFSET=3600000]