Help with Definition Query for Current Date minus 7 days

Question

Hello community, I could really use some help!  Our Crime Analysis Center has recently transitioned over to using Arc Server and sde.  Our data is stored in SQL and we have a point feature class that is joined to the table through a view.

We are trying to set up definition queries for different crime types to show the incidents that occurred over the past 7 days so we won't have to continually change the date range in the definition query.  I have seen in other posts to use CURRENT_DATE but I am not familiar with SQL language (not yet at least) and don't know how to structure the minus 7 days part.

This is a huge hurdle in our process and finding the answer would save us countless man hours a week.  We also prepare analysis based on the past 4 weeks and 6 weeks of data so you can see how challenging it would be to have to keep changing the date ranges by hand.  Thank you in advance for your help!

Sincerely,
Paula F. Cutrone
Lead Crime Analyst
Onondaga Crime Analysis Center
Syracuse, NY

JoeBorgione · Answer

Can someone provide an example that will work with a Personal Geodatabase?  The above examples produce an error message when I use them.

I have tried this syntax:
... AND Date_Site_Mod > GETDATE() -3

Google "datediff function access"...

DavidWheelock · Answer

Can someone provide an example that will work with a Personal Geodatabase?  The above examples produce an error message when I use them.

I have tried this syntax:
... AND Date_Site_Mod > GETDATE() -3

PaulaCutrone1 · Answer

Gentlemen thank you for your responses.  Steve, your method hit the nail on the head! I am beyond excited to implement some changes today.  I will have to talk to my vendor about filtering at the view level since there are multiple analysts using that same view for different queries.  Either way, your solution is going to greatly improve our time savings.  Thanks again!

Paula F. Cutrone
Lead Crime Analyst
Onondaga Crime Analysis Center
Syracuse, NY