topic Re: Arcsdesqlexecute from arcpy of desktop 10.5 cannot read date column in Python Questions

Arcsdesqlexecute from arcpy of desktop 10.5 cannot read date column

ChrisPedrezuela — Mon, 03 Jul 2017 08:21:05 GMT

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

Re: Arcsdesqlexecute from arcpy of desktop 10.5 cannot read date column

JoshuaBixby — Mon, 03 Jul 2017 15:40:10 GMT

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.

Re: Arcsdesqlexecute from arcpy of desktop 10.5 cannot read date column

ChrisPedrezuela — Mon, 03 Jul 2017 19:47:51 GMT

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.

Re: Arcsdesqlexecute from arcpy of desktop 10.5 cannot read date column

JoshuaBixby — Tue, 04 Jul 2017 12:40:26 GMT

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.

Re: Arcsdesqlexecute from arcpy of desktop 10.5 cannot read date column

ChrisPedrezuela — Tue, 04 Jul 2017 21:01:05 GMT

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,.

Re: Arcsdesqlexecute from arcpy of desktop 10.5 cannot read date column

BlakeTerhune — Wed, 05 Jul 2017 19:30:24 GMT

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

Re: Arcsdesqlexecute from arcpy of desktop 10.5 cannot read date column

ChrisPedrezuela — Wed, 02 Aug 2017 08:57:54 GMT

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.

Re: Arcsdesqlexecute from arcpy of desktop 10.5 cannot read date column

SvivaManager — Thu, 10 Aug 2017 10:14:55 GMT

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.

Re: Arcsdesqlexecute from arcpy of desktop 10.5 cannot read date column

SvivaManager — Thu, 10 Aug 2017 10:21:54 GMT

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.

Re: Arcsdesqlexecute from arcpy of desktop 10.5 cannot read date column

VinceAngelo — Fri, 10 Dec 2021 21:57:14 GMT

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

Re: Arcsdesqlexecute from arcpy of desktop 10.5 cannot read date column

BlakeTerhune — Wed, 25 Oct 2017 16:49:23 GMT

We're on Oracle 12c and ArcGIS 10.5.1 and have this problem. Querying a date field either returns an empty string or errors. Had to use to_char() to convert the date to text like Chris Pedrezuela and Vince Angelo mentioned.