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

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?


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

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:

FROM [UG Journey Master]
WHERE [dateofApplication] >= convert(date,getDate()-15)
and [dateofApplication] < convert(date,getDate()-14)
  • Follow up question @AdamSpriggs - I am currently constructing a query that will add people into a data extension when their event_date is 28, 21, 14 or 7 days away. I currently use WHERE [Event_StartDate] >= DateAdd(day,28,cast(GetDate() as date)) AND [Event_StartDate] < DateAdd(day,29,cast(GetDate() as date)) to get those 28 days away, but how would I write the rest of it to add those people 21 or 14 days away? I'm not sure whereabouts the 'ORs' and 'ANDs' need to go. – TCassa Feb 9 '17 at 15:34
  • Please post a new question. – Adam Spriggs Feb 9 '17 at 16:00

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.