Dynamic Dates

 

Dynamic dates in queries can be much more useful than explicit dates because they are based on the current date. The following methods handle common scenarios. The following examples use the Domain Time Token token, but could also use the User Time Token or GETDATE().

Note: In all examples, replace [date_field]'s with the specific date field you are using (such as Contact [Date_of_Birth] or [Event_Start_Date]).

Seven Days Ago

This technique can be used to calculate a date N days ago, such as 7, 30, or 60 days ago. 

dp_DomainTime - 7

Previous Calendar Month

MONTH([date_field]) = MONTH(DATEADD(m, -1, dp_DomainTime)) AND YEAR([date_field]) = YEAR(DATEADD(m, -1, dp_DomainTime))

Current Calendar Month

MONTH([date_field]) = MONTH(dp_DomainTime) AND YEAR([date_field]) = YEAR(dp_DomainTime)

Next Calendar Month

MONTH([date_field]) = MONTH(DATEADD(m, 1, dp_DomainTime)) AND YEAR([date_field]) = YEAR(DATEADD(m, 1, dp_DomainTime))

See Also