Date return issue with ArcSDESQLExecute

CassKalinski

I am converting a script that uses pyodbc for a couple of select statements to using ArcSDESQLExecute instead. The change works fine on most of the data retrieval but I am hitting an issue on a date field in one of the queries.

With pyodbc, the field is returned as a date object. With ArcSDESQLExecute, the date comes back as a string like 'mm/dd/yyyy'. No hours, minutes, etc. I need either a date object (preferred) or a string with the full %Y%m%d%H%M%S format.

Is there a flag I am missing? A change needed in the SQL statement?

* MS SQL data base
* Field is type datetime in the database
* Records are timestamps every ~15 minutes, so having the full date object is important
* Same SQL string used in both pyodbc and ArcSDESQLExecute and it worked fine before the update.

def _get_timeseries_data(start, end, senID):

# start = datetime object
# end = datetime object

DB_TIMESERIES = '[{}].[{}].[timeseries]'.format(DB,SCHEMA)
DB_CONN = r'C:\Users\blah\blah\blah\Connection to 10.20.2.5.sde'
cnx = arcpy.ArcSDESQLExecute(DB_CONN)

# [Timestamp] = table field of type datetime (don't like the naming, but that is from the app vendor)
# Example from db: 2014-11-17 14:49:01.000

sqlString = "SELECT [Timestamp], [RecordedValue], [GradeName], [ApprovalName] \
FROM {} WHERE (SensorID = '{}') AND (Timestamp BETWEEN CONVERT(datetime, '{}') AND CONVERT(datetime, '{}'))\
ORDER BY Timestamp"\
.format(DB_TIMESERIES, senID, start, end)

rows = cnx.execute(sqlString)
logging.debug('TS query row: %s', row)
dRows = []
for row in rows:
    dRows.append([row[0], row[1], row[2], row[3]])

del cnx
return dRows

blake.terhune

Try formatting the date in the SQL when you query it. Here's your query string but formatted with Python multiline string.

 

sqlString = """SELECT FORMAT([Timestamp], 'yyy-MM-dd HH:mm:ss.fff') as Timestamp,
  [RecordedValue], [GradeName], [ApprovalName]
  FROM {}
  WHERE (SensorID = '{}')
  AND (
    Timestamp BETWEEN CONVERT(datetime, '{}') AND CONVERT(datetime, '{}')
  )
  ORDER BY Timestamp
""" .format(DB_TIMESERIES, senID, start, end)

 

Sorry, I work with Oracle databases so I apologize if something in that syntax is slightly off.