0

SFMC Limitation: We can't use variables Self Limitation: No usage of SSJS

I use SQL Query Activity to get the previous day's records from Midnight to Midnight, just like a Tracking Extract might.

e.g.

WHERE
  u.EventDate BETWEEN DATETIMEFROMPARTS ( YEAR(DATEADD(DAY, -1, GETDATE())), MONTH(DATEADD(DAY, -1, GETDATE())), DAY(DATEADD(DAY, -1, GETDATE())), 00, 00, 00, 000 )
  AND DATETIMEFROMPARTS ( YEAR(DATEADD(DAY, -1, GETDATE())), MONTH(DATEADD(DAY, -1, GETDATE())), DAY(DATEADD(DAY, -1, GETDATE())), 23, 59, 59, 997 )

What I am trying to understand is; is the GETDATE() function deterministic, e.g. evaluated once per the SELECT statement or it non deterministic, e.g. evaluated each time it is called?

Is there any other way of doing this as well where I don't need to repeat DATEADD(DAY, -1, GETDATE())?

Thanks

1

Here's the sargable method that I typically use to get yesterday's records:

select
o.subscriberkey
from _open o
where
o.eventdate >= convert(date, getDate()-1)
and o.eventdate < convert(date, getDate())
and o.isunique = 1

Converting to date effectively resets time portion to midnight and using a range captures all times between midnights.

Appears getDate() is non-deterministic.

2
  • If it is sql server 2005 or older it will be deterministic, if it's newer then it is non-deterministic. I couldn't find anywhere which version we were using. This helps simplify tho, thanks.
    – Jordan
    Oct 6 at 13:15
  • It’s SQL Server 2016, so non-deterministic as shared by Adam Nov 12 at 23:05

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