nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Find more posts tagged with

Accepted answers

All comments

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

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.

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.

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:

<SPAN class="keyword token">SELECT</SPAN> idcol<SPAN class="punctuation token">,</SPAN>cast<SPAN class="punctuation token">(</SPAN>to_char<SPAN class="punctuation token">(</SPAN>datecol<SPAN class="punctuation token">,</SPAN><SPAN class="string token">'YYYY-MM-DD'</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> <SPAN class="keyword token">varchar</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="number token">10</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> datecol_str
<SPAN class="keyword token">FROM</SPAN>&nbsp; mytable‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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

- V

VasudhaChaturvedi_2-1663251202655.png

VasudhaChaturvedi_3-1663251251250.png

blake.terhune

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.

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.