I want to retrieve based on modifieddate(updated) field records count between start date time and end date time in my data extension. How to include startdatetime and enddatetime in lookuprows for modifieddate. I tried like below. any one help me out on this.

set @StarttimeString = "12:00:00 AM"
set @EndtimeString = "11:59:59 PM"
SET @TodayDateTime= Now()
Set @Formatdate=Format(@TodayDateTime, "d", "Date")

set @dateConcatStart = Concat(@Formatdate, " ",@StarttimeString)
set @dateConcatEnd = Concat(@Formatdate, " ",@EndtimeString)


dateConcatStart: %%=v(@dateConcatStart)=%% </br>
dateConcatEnd: %%=v(@dateConcatEnd)=%% </br>

i have tried like this

SET @TodayDateTime= Now()
set @rows = LookupRows("TestDE","modifieddate",@TodayDateTime)
set @rowCount = rowcount(@rows)

its not working, why means now () value current datetime. But record is modified Now() before or after in same day.

  • You need to perform LookUpRows or a similar function and since you want to count the rows, you need to do perform the rowcount function. Your question does not show that you tried to use the lookuprows function. Have you already been searching for an answer on this platform? Commented Jan 21, 2021 at 6:30
  • 1
    I have edited question sofar what i am researched, please find question below Commented Jan 21, 2021 at 7:00
  • AMPscript will only do an exact comparison, so you'd need to be comparing two exact values. If you are storing the modified date as a full datetime, you won't be able to use AMPscript. If you want to search for values between two times you will need to use SSJS - see salesforce.stackexchange.com/questions/201653/… Commented Jan 21, 2021 at 23:11

1 Answer 1


First of all welcome.

General notes:

Everything in your usecase points towards SQL instead of AMPScript.

LookupRows(), and all script based retrievals in SFMC have a row limit of 2000 / 2500 rows respectively. Keep that in mind when deciding what to use.

LookupRows and related functions work with precise matches, there are no ranges or other comparisons (greater than etc.). "Today" in your definition (from 00:00 - 23:59) is a range. It is thus unsuitable for LookupRows and you will have to go to great lengths making this work, ignoring much simpler solutions. There are functions for it like datePart, or dateDiff. https://ampscript.guide/datepart/

In contrast, Now() is not a range. Now() returns a timestamp, a precise point in time, think of it as "this current millisecond" (it's even more precise I think). That is absolutely technically suitable for a lookup, but terrible in terms of common sense. I would absolutely expect that in a real life usecase, looking for rows that match the current millisecond always returns zero.

So typically, SQL is the better choice systemically speaking, it scales, and I would argue it also it a lot easier to read and write here. it also has a simple count function.

Also, before you make any date comparisons (and before you use this code!):

Be clear on what your data extension field's (here: modifiedDate) timezone is. If the system set a timestamp automatically, it's servertime. If some external source sets a timestamp, find out what timezone that is first (through testing).

Then, make those timezones clear in your fieldnames and variables to remind yourself what you are comparing. Here in europe, UTC-6 is pretty unintuitive, so I've learned to be careful.

note I changed the name of modifiedDate to modifiedDateServerTime to illustrate my point.

Select count(someFieldName)
WHERE DAY(modifiedDateServerTime) = DAY(GETDATE())
AND MONTH(modifiedDateServerTime) = MONTH(GETDATE())
AND YEAR(modifiedDateServerTime) = YEAR(GETDATE())

This could be the end of story.

A lot of the things in your code suggest that you need a better grasp on what a "date" is for a computer as opposed to for a person. A date is not just a series of characters that you concat and cut (that would be a "string").

A date has specific properties, e.g. a Day, Month, Year part, which you can split it into, or a timezone, or a format. Understanding those is key to get any foot on the ground. For example, you do not need to explain the server that a day starts at 00:00 and ends at 23:59. The server understands this well, but it works in its servertime (UTC-6, no daylight savings time observed).

As for AMPscript, I understand this is the actual question in the headline. I do NOT recommend this approach, I just try to answer the literal question to illustrate my point. code is untested.

It's fundamentally a similar basic approach to SQL above but WAY more complicated with tons of stupid legwork - this is simply not how this "should" be done.

Break now into parts:

set @currentTimestampServertime = Now()
set @currentYearServertime = datePart(@dateString, "Y")
set @currentMonthServertime = datePart(@dateString, "M")
set @currentDayServertime = datePart(@dateString, "D")

start counting at zero:

SET @count = 0

Get all rows somehow into a rowset. Time for our first workaround! Insert a column into your DE that always has the same value, we call it "include" and set the value to 1.

this is a representation of yourDE, not code.

1234,[email protected],Sun, 15 Oct 2017 19:35:00,1
4564,[email protected],Sun, 15 Oct 2017 19:36:00,1
6487,[email protected],Sun, 15 Oct 2017 19:37:00,1

This is a hack to make your entire data extension useable for a lookupRows. BUT it only works for 2500 rows at a time!

now you can lookup "all" (the first 2500) rows:

set @myFirst2500Rows = LookupRows("TestDE","include",1)
set @rowCount = rowcount(@myFirst2500Rows)

Now for each row, you can compare the modifiedDate's PARTS (day, month, year) with the parts in NOW(). Which means you need a for loop. If for a row we find a match between the parts, we add 1 to our count. After closing the loop, we write 1.

IF @rowCount > 0 THEN
  FOR @i = 1 TO @rowCount DO
    SET @row = row(@myrowset,@i) /
    SET @modifiedDateServertime = field(@row,"modifiedDateServertime")
    IF @rowCount > 0 THEN
         FOR @i = 1 TO @rowCount DO
         SET @row = row(@myrowset,@i) /*get row based on loop counter */
         set @modifiedDateYearServertime = datePart(@modifiedDateServertime, "Y")
         set @modifiedDateMonthServertime = datePart(@modifiedDateServertime, "M")
         set @modifiedDateDayServertime = datePart(@modifiedDateServertime, "D")

         IF modifiedDateYearServertime = @currentYearServertime
         AND @modifiedDateMonthServertime = currentMonthServertime
         AND modifiedDateDayServertime = currentDayServertime

  NEXT @i


bottom line: using AMPScript we have done a ton of work just to have a solution that breaks at 2500 records and requires the next workaround (a while loop). Please use SQL.

  • Thank you for your answer. Yes i have used SQL, its working like a charm Commented Jan 22, 2021 at 5:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .