topic Re: Query Date on a FeatureClass joined with Table from SQL SERVER 2008 R2 in Data Management Questions

Query Date on a FeatureClass joined with Table from SQL SERVER 2008 R2

NoureddineEl-zaatari — Tue, 28 Dec 2010 06:27:11 GMT

ENVIRONMENT:

ArcGIS DESKTOP 10 SP0
ArcSDE 10
SQL SERVER 2008 R2

hello,

im going through something weird here..

I have a feature class joined with a table from SQL SERVER, the datasource shows properly no problem, but when i try to attribute query by date selecting the value from the unique list, i get a message "expression verified but it retuned no results" !!

Digging more into this, i viewed the bussiness table in sql server and noticed that its sotres the date with "00:00:00" so i tried to query with 'date 00:00:00' but i got a message "expression verified but it retuned no results" !!

trying to work it out, i tried to query the datatable that is added the mxd when you do a join and this time it returned an error "Invalid Sql Statement" although i have built the expression using arcmap expression builder and selected the value from the unique list.

i have attached a word 2003 document with all the screen shots with description hopping that someone can help with this

thank you

Re: Query Date on a FeatureClass joined with Table from SQL SERVER 2008 R2

VinceAngelo — Tue, 28 Dec 2010 11:16:49 GMT

Not everyone has Word on their computer, and even those that do might not be willing to
open documents posted on the web (macro virus threat), and even if they are, there may
be compatibility issues. The best format for posting screenshots is JPEG; yes, it requires
a bit more work to save a "bump" as .jpg, but if you're looking for assistance, it makes it
easier for the folks you want to read the documents to open them.

Date types always have a certain level of imprecision to them -- especially when the database
leaves it to the client app to determine timezone. Equivalence tests are unlikely to result
in a TRUE evaluation unless they are exact to the second (or even millisecond). The safe
way to do date comparisons is to construct a range test ( "datecol >= {date}00:00:00 AND
datecol < {date+1}00:00:00" ). You can sometimes use the BETWEEN SQL operator (e.g.,
"WHERE expr BETWEEN v1 AND v2"), but on some implementations BETWEEN evaluates as
"expr > v1 AND expr < v2", which would miss values equal to v1.

- V

Re: Query Date on a FeatureClass joined with Table from SQL SERVER 2008 R2

NoureddineEl-zaatari — Wed, 29 Dec 2010 02:06:07 GMT

thank you vangelo for your response,

i tried the range comparison that you have supplied in your reply but it didnt work as well, i have attached screen shots as .JPG files as you have suggested

your support is highly appreciated,

thank you

Re: Query Date on a FeatureClass joined with Table from SQL SERVER 2008 R2

NoureddineEl-zaatari — Wed, 29 Dec 2010 02:08:43 GMT

one last screenshot i couldnt attach due to upload size limit

Re: Query Date on a FeatureClass joined with Table from SQL SERVER 2008 R2

VinceAngelo — Wed, 29 Dec 2010 11:00:53 GMT

You need to use the date functions and/or operators appropriate to your RDBMS to
implement date ranges, not copy my conceptual notation literally. It might help to
work on this in SQL, without ArcGIS involved at all. Once you know how to build
a valid query, integrating it with ArcGIS will be less difficult.

- V

Re: Query Date on a FeatureClass joined with Table from SQL SERVER 2008 R2

KelsiJurik — Tue, 29 Nov 2011 17:46:53 GMT

I have had success in Query Builder on a Joined table using an unconventional syntax:
[your date field] > #2001-01-01#
or, it also seems to work with
[your date field] > #2001/01/01#

This seems to work even though the dates are displayed in the attribute table as MM/DD/YYYY