topic Re: Does SDE support the SQL Server 'date' field? in Data Management Questions

Does SDE support the SQL Server 'date' field?

LeeKaufman — Fri, 02 Mar 2012 17:33:48 GMT

I have been trying to register a SQL Server 2008 table with a 'date' field as a point feature class in SDE 10. When I try to register the table I get the following error.

sdelayer -o register -l test,geom -e p -t geography -R 1 -i sde:sqlserver:localhost,1276 -D <%databasename%> -E -180,-90,+180,+90 -u <%user%> -p <%password%>   ArcSDE 10.0  for SQL Server Build 685 Fri May 14 12:05:43  2010 Layer    Administration Utility ----------------------------------------------------- Error: Attribute conversion error (-55). Error: Unable to create registration for table test.

If I remove the 2 date fields, change them to datetime or datetime2 then the table registers just fine.

I have seen here that SDE uses the datetime field and does not mention that the date field is NOT supported.

http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/A_comparison_of_ArcGIS_ArcSDE_and_SQL_Server_data_types/002q00000068000000/

But it also says

When you create a feature class or table in ArcGIS, there are 11 possible data types you can assign to a field. That means there are some data types that can only be created outside ArcGIS.

leading me to think that I can use the date field as long as the table is created outside of SDE, which it is.

Re: Does SDE support the SQL Server 'date' field?

JonDeRose — Fri, 02 Mar 2012 18:35:27 GMT

The SQL Server Date() and Time() data types are not currently supported.

The entire list of data types supported by ArcSDE with SQL Server are found within the link you reference. The reference to the 11 datatypes is taken from the 11 unique types listed under the "ArcGIS data type" heading, meaning that those within the listing without a corresponding ArcGIS data type "na" must be created outside of the ArcGIS UI.

A comparison of ArcGIS, ArcSDE, and SQL Server data types
http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/A_comparison_of_ArcGIS_ArcSDE_and_SQL_Server_data_types/002q00000068000000/

As you are using ArcGIS 10 and the SQL Server Geography Spatial Type you can access features using this data type via Query Layers to query etc. This may be of use unless you must edit these using ArcGIS. (this also goes for the Geometry type)

- Jon

Re: Does SDE support the SQL Server 'date' field?

LeeKaufman — Fri, 02 Mar 2012 19:15:23 GMT

Thanks Jon. I will look into the Query Layers.

Lee

Re: Does SDE support the SQL Server 'date' field?

harleypowers_parks — Fri, 07 Sep 2012 22:16:44 GMT

Hi. I'm in ArcGIS 10.1 and it appears Date is not supported for SqlServer
we tried changing the date data type to a datetime data type..and still not working.
anyone having same issue?

Re: Does SDE support the SQL Server 'date' field?

MegPeterson — Wed, 03 Oct 2012 17:43:37 GMT

Hi Harley - I'm running into a similar problem.

Server 10.1 using a direct connect (standard I guess now...) over to SqlServer 2008. I have a DATE field in a web-editing application and it's throwing out some interesting results.

I'm tempted to update the geodatabase to 10.1 but a little nervous about doing so. Were you able to solve your issue by changing the field to another type?

Thanks, Meg

Re: Does SDE support the SQL Server 'date' field?

KimPeter — Thu, 04 Oct 2012 19:10:02 GMT

Hi Meg,

As indicated, the date data type isn't supported at 10.1 either, so upgrading will not change that.

http://resources.arcgis.com/en/help/main/10.1/#/SQL_Server_data_types_supported_in_ArcGIS/002q00000068000000/

I'm not sure why datetime would not have worked, though. Fields of that data type should be visible in ArcGIS.