Problem viewing SQL 2008 "Date" field

Question

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.

• David Jenness: 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.
• Kirk Kuykendall: Did you try using the Make Query Table tool on the view?
• David Jenness: 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.
• PolyGeo: 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.
• user28030: 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.

Answer 1

If you look at this supported database types diagram you will see that date is not supported and datetime2(7) should be used instead for microsoft sql server.