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)
FROM
yourDataExtension
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.
subscriberkey,email,modifiedDate,include
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
THEN
Add(@count,1)
ENDIF
NEXT @i
]%%%%=v(@count)=%%
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.
