[SalesForce] Marketing Cloud Query activity using DateAdd() to get records

I am trying to use this query to add records with a 'dateofApplication' date of 15 days ago:

SELECT * 
FROM [UG Journey Master]
WHERE [dateofApplication] = DateAdd(day,-15,GetDate())

Unfortunately, it adds 0 records to my data extension.
The first part is correct, as when I use a different WHERE clause (such as Email_Address Like 'Z%') works fine. So there is something wrong with my DateAdd clause.

Can anyone help?

Best Answer

You'll need to consider the time portion of the dates. Here's a sargable way to do it:

SELECT * 
FROM [UG Journey Master]
WHERE [dateofApplication] >= DateAdd(day,-15,cast(GetDate() as date))
and [dateofApplication] < DateAdd(day,-14,cast(GetDate() as date))

Casting getDate() as a date, will reset the time part of the date to zero (midnight).

Update (20170804)

Here's a more efficient way to do it:

SELECT * 
FROM [UG Journey Master]
WHERE [dateofApplication] >= convert(date,getDate()-15)
and [dateofApplication] < convert(date,getDate()-14)
Related Topic