I am writing a query that will add people into the data extension if the event is starting in 1 week time. So the idea is to trigger the reminder email 1 week before the event.

The snippet of query is below:

Select * where start_date = DATEPART(dd, DATEADD(dd,-7,GETDATE()))

The query works fine. But the issue is I need to add 10 hours to offset the US server time so that it will match my local time.

For other date related qeuires I have been doing this

start_date = DATEPART(dd, DATEADD(hh,10,GETDATE()))

How can I combine these two so that I can add the people 7 days before the event into DE and also consider the time difference?

  • Why are you using 5 as opposed to 7 in your DATEADD – Samuel Dare Sep 27 '17 at 11:18
  • Hi @Data_Kid, it should be 7. I have edited the typo error. – kLo Sep 28 '17 at 1:49

Try setting the start_date in hours in the first one i.e 610 (if you are ahead) or 590 (10 hours behind).

/* 10 Hours Ahead */
FROM [Data_Extension]
start_date = DATEPART(dd, DATEADD(hh,-590,GETDATE()))

/* 10 Hours Behind */
FROM [Data_Extension]
start_date = DATEPART(dd, DATEADD(hh,-610,GETDATE()))
|improve this answer|||||
  • Thanks. It did not work for me. Without the date filter, I got some records in my DE. But after adding date filter, no records returns. – kLo Sep 28 '17 at 9:07

I generally do these types of queries like this:

, de.start_date
from [Data_Extension] de
where de.start_date >= convert(date, getDate()-7)
and de.start_date < convert(date, getDate()-6)

This method of doing the date criteria handles the time portions of the dates properly and is more efficient (sargable) than using multiple date functions. You can simply subtract days from getDate() without another function.

Also, I'd advise against using select * in your queries. The column names are cached, so if you add a column later, it may not get selected in your query.

|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.