Im trying to use a lookupordered row expression to find rows that have a submission date of today and display them, unfortunately I am not finding any matches. I am unsure if my syntax is incorrect or my understanding of date time is incorrect.

VAR @currentSystemTime, @Today SET @currentSystemTime = NOW()
SET @Today= FormatDate(SystemDateToLocalDate(@currentSystemTime), "YYYY-MM-DD")

var @rows, @row, @rowCount, @numRowsToReturn, @lookupValue, @i
set @numRowsToReturn = 20 /* 0 means all, max 2000 */
set @rows = LookupOrderedRows("New-OPT_in_response-TODAY",@numRowsToReturn,"Clinician_Email desc, SubmissionDate asc","SubmissionDate", @today)
set @rowCount = rowcount(@rows)

if @rowCount > 0 then

  for @i = 1 to @rowCount do

    var @Clinemail, @date
    set @row = row(@rows,@i) /* get row based on counter */
    set @clinemail = field(@row,"Clinician_Email")
    set @subdate = field(@row,"SubmissionDate")


    Row %%=v(@i)=%%, Clinician Email is %%=v(@clinemail)=%%, the time was %%=v(@subdate)=%%

  next @i ]%%

%%[ else ]%%

No rows found

%%[ endif ]%%
  • Have you tried displaying the @today variable and seeing if it matches the format of Submission Date? – zuzannamj Apr 1 at 21:05

I used this solution here - AMPScript to retrieve TODAY created rows on an email

It appears the issue is related to date time being the full value and not in the format expected, I ran a query using date time then stamped a lookup value in the DE, this works.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.