I would like to query the Marketing Cloud _opens table to find any opens in the last 7 days but from the beginning of that given day.

Using the following will get the last 7 days relative to the current time:

select SubscriberKey, EventDate
from _opens
where EventDate => (s.EventDate > getdate()-7)

pseudo code fyi

So as of now (2018-08-06 15:37:00) it will go back to 2018-07-31 15:37:00 but I want the beginning of the day so its 2018-07-31 00:00:00.


Try pulling only date from getdate() and then adding the 0 time to it. Something like below:

SELECT SubscriberKey, EventDate
FROM _Open
WHERE EventDate >= convert(char(14),DateAdd(DD,-7,GETDATE() ),121) +'00:00'
| improve this answer | |

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.