[GIS] 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.

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.