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

[DanPatterson]

Code formatting ... the Community Version - GeoNet, The Esri Community would help readability

[BlakeTerhune · Accepted solution]

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.

[CassKalinski]

Excellent! Worked as advertised.
Thank you Blake!

[CassKalinski]

The actual code snippet then...

def _get_timeseries_data(start, end, senID):

    dRows = []
    cnx = arcpy.ArcSDESQLExecute(DB_CONN)

    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)
    try:
        rows = cnx.execute(sqlString)
        for row in rows:
            logging.debug('TS query row: %s', row)
            # With pyodbc, used fTimestamp instead of row[0] in the append...
            # fTimestamp = row.Timestamp.strftime("%Y-%m-%d %H:%M")
            fValue = _format_readings(row[1])
            dRows.append([row[0],
                fValue,
                row[2],
                row[3]])

    except Exception as e:        
        print('SQL call failed. SQL: %s. Error: %s', sqlString, e)

    del cnx

    return dRows