Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I am using ArcMap Basic 10.1 SP1 and I'm connected to a permitting database stored in a SQL Server 2008 R2 database.

I am trying to pull the tabular data over into my ArcGIS Desktop Project so that I can do a join with the permit boundary information based on permit numbers.
One of my fields in the SQL Server database is called "DateIssued" and it is of SQL datatype "date" rather than "datetime". When I drag over my table from my "Database Connections" area of Catalog, all the fields come over except those marked as "Date" and work fine. The "Date" items are simply not listed as a field.

I tried to get around this by making some SQL views to try to CAST or CONVERT the SQL data to be a datetime or even a varchar, but then when I drag the view over, it gives me the error message that "Warning - Could not add the specified data object to the map. Invalid SQL syntax" To be honest, I am not sure that ArcGIS 10.1 even supports using the SQL views as I seem to get that message on any views.

Has anyone run into this problem with the SQL "date" data type? I thought I might generate some temporary tables to do the date to datetime conversion to see if that might work. I'll post my findings here as well.

share|improve this question
    
Update, I just created a temporary table with the only difference being that I used the datetime datatype and it worked fine in ArcMap, so the "date" datatype does seem to be the culprit so looking for people that have had similar experiences and how they worked around it. –  David Jenness May 30 '13 at 13:01
    
Did you try using the Make Query Table tool on the view? –  Kirk Kuykendall May 30 '13 at 13:59
    
Thanks for the tip. Looks like it does the same thing... It omits the "date" fields even before the tool runs. It shows the "datetime" fields just fine. As a workaround I just set a stored procedure to dump the data to a temporary table where I have it set as "datetime" and that does the trick, but I'm still curious to see if anyone else has tried working with the SQL 2008 "date" datatype. –  David Jenness May 30 '13 at 21:15
    
Would you be able to edit your question to further refine it, please? Having learnings as comments is better than nothing but the aim of GIS-SE and its sister sites is to go back and improve both questions and answers. –  PolyGeo Jul 17 '13 at 3:28
    
Identified the same issue today - we're using ArcGIS 10.2 and SQLServer Express; A local ODBC database connection wouldn't show DATE fields in ArcGIS Catalog for a few tables in our SQLServer database. Prior to update to 10.1/10.2, this wasn't a problem. Changing these field types in these few tables from DATE to DATETIME in SQLServer allowed us to see these fields in ArcGIS Catalog as expected. Thanks for the hints that allowed me to resolve our problem here. –  F Moffatt Mar 13 at 18:37
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.