Arcsdesqlexecute from arcpy of desktop 10.5 cannot read date column

[ChrisPedrezuela]

We just recently upgraded from 10.3 to 10.5 and noticed a python script started failing. Based on investigation this class, arcsdesqlexeCute, is causing the issue. For some reason, the returned rows from using this class cannot read date values. Testing on 10.3 arcpy installation, this same workflow runs fine and returns date values properly. Just want to check if this is a bug. Thanks

[JoshuaBixby]

Did you update the clients (ArcMap, ArcEngine, etc...) to 10.5 or ArcGIS Server to 10.5?  What versions are the clients and servers running.  Also, were the database platforms upgraded at all in terms of DBMS version?  What DBMS type and version are you running.

It is helpful if you can post snippets that contain code that worked in 10.3 and code that doesn't work in 10.5 along with the specific error messages.  There isn't quite enough here for anyone to comment on specifically.

[ChrisPedrezuela]

Hi Joshua,

The custom script is scheduled on a server where ArcGIS Server 10.5 is running. The test I did was done on Desktop 10.5 using featureclass\table on a 10.5 SDE and 2012 SQL server instance. Python install version we are using is 2.7.12. The code below is simple one to test,

egdb = r'dataconnection\somedb.sde'

egdb_conn = arcpy.ArcSDESQLExecute(egdb)

sql = '''SELECT DATEFIELD FROM SOMEDATA'''

egdb_return = egdb.execute(sql)

the tuple returned by running this piece of code using the 10.5 setup (on server and desktop) will yield blank strings while when using just a 10.3 desktop install on a separate machine it extracts the datetime values correctly.

[JoshuaBixby]

Interesting, I am seeing the same thing using ArcMap 10.5.1 with Server 10.5.1 on SQL Server 2016, i.e., empty strings for date values and None for NULL.  I am not sure if it is a SQL Server issue, ArcGIS Server issue, or ArcGIS Desktop issue.  Seeing it is so straightforward and reproducible, I would open a Support case with Esri because something isn't working correctly.

[ChrisPedrezuela]

Thanks for testing Joshua. The same issue was reproduced by our local ESRI distributor. Also just to correct myself, we are using SQL 2016. I was thinking it was the database side but if I use 10.3 arcpy on the same database, I still get the timestamp values.  I'll update this thread once I get more feedback from our local distributors support team,.

[BlakeTerhune]

We're currently preparing to upgrade to 10.5.1 so I'm interested to see how this turns out.

[ChrisPedrezuela]

we spoke with our local ESRI vendor and currently the only solution is a workaround. On your sql query you can cast the date field as varchar. As string value the datestamp will be read. From here you would have to convert the string to datetime.

[VinceAngelo]

To clarify, the result of a to_char(datecol,'YYYY-MM-DD') is a text field (which is interpreted as a CLOB column, and caused an issue with ArcGIS 10.5 on PostgreSQL).  The  syntax to cast the string conversion would look like:

SELECT idcol,cast(to_char(datecol,'YYYY-MM-DD') as varchar(10)) as datecol_str
FROM  mytable‍‍‍‍‍‍‍‍

Make sure the VARCHAR width is wide enough to hold the formatted date!

- V

[SvivaManager]

I'm having the exact same problem with older versions. ArcGIS Server 10.4.1 and MSSQL 2012. I did cast to begin with but it slows down the query and the script takes 7 minutes to finish instead of a few seconds!! Is there a bug for this in ESRI??

Shay.

[SvivaManager]

Did you ever tried using a different client instead of arcpy.ArcSDESQLExecute to query the database? from different posts I found, people says it's not a good idea but I didn't tried it.

Shay.