Help with Definition Query for Current Date minus 7 days

[PaulaCutrone1]

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

[VinceAngelo]

ArcSDE supports five different RDBMSes that use SQL, all of which have different syntax
for referring to dates and date functions.  Even using just Microsoft SQL-Server, there
are multiple possible flavors (2005, 2008, 2008R2).  Dates can also be difficult due to
variability of precision -- does a time reference to a day without hour imply midnight
or noon, and in what timezone?

What you're looking for probably involves the SYSDATETIME() function and some variant
of DATEADD or DATEDIFF --

where date_col > DATEADD(day,-7,SYSDATETIME())

or

where DATEDIFF(day,date_col,SYSDATETIME()) < 7

but that depends on the exact types involved.  This documentation page is probably a
good starting point, but you'll likely need to experiment, since different variants of the
same expression are likely to give different performance, depending on how the index
interacts with the expression.

- V

[SteveVidal]

Hi Paula,

I am actually doing something very similar to what you're doing, for traffic related incidents (drink driving, etc.).

If you are using SQL Server, you can use the GetDate() function in your definition query, something like: OccurrenceDate > GetDate() - 7

Or alternatively, you can set that filter in your view itself, so that you end up with a set of views in SQL server (e.g. last 7 days, las 30 days, etc.) and then you can join your point feature to the individual views without the need for a definition query. The advantage of setting the filter in the database itself is that is is easier to test and also to make sure that you're not mixing different locales (e.g. in our install ArcSDE stores dates as UTC).

Also, if you are using ArcSDE 10, you can use query layers to bring the views into your mxd.

Hope this helps,

Steve Vidal
Solutions Architect, I-TAS Project
Intelligent Traffic Policing Program
-------------------------------------------------
Operations Support Command
Queensland Police Services

[PaulaCutrone1]

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

[DavidWheelock]

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

[JoeBorgione]

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