esriFieldTypeDate field - Query Builder SFV 1.3

[SusanMordy1]

My map service layer has several esriFieldTypeDate fields.

When I query in ArcMap, the format returned in Select by attriburtes is this: TO_DATE('2024-12-31 00:00:00','YYYY-MM-DD HH24:MI:SS')
as the data resides in Oracle SDE.

The actual records look like this: 01/12/1989

When I go to query these fields in the SFV 1.3 query builder, the returned format looks like this: 12/31/2020 12:00:00 AM
and I get an error message "query failed!'

Any ideas what need to be fixed/changed? I have attached a few graphics to illustrate my problem.

The AGREEMENT_END_DATE = TO_DATE('2019-12-31 00:00:00','YYYY-MM-DD HH24:MI:SS') query works fine in ArcMap and the REST map service query layer function. Not sure why I get  AGREEMENT_END_DATE = '12/31/2020 12:00:00 AM' when I use this field in the query builder (SFV 1.3) widget.

Thanks for your help,

Susan

[MelissaPrindiville]

Was this question ever resolved?  I have a similar issue in SQL SDE.

[Anonymous User]

Original User: violet559

I did not figure this out myself (ran out of time), but I have posted the response I received from ESRI techincal support regarding this issue. Hope this helps.

****************************************************

The Query Builder widget is essentially just a form, it has no idea what
the DBMS management system is.  What you type into the Filter box is
just passed to the server as a request.  The server then passes that
string to the DBMS.  Thus that string, MUST match the syntax
requirements of the DBMS.  I was only suggesting that you use ArcMap in
order to understand what the syntax requirements are for ORACLE. 

What you really need is to add intermediate code that translates a
simple date entered by the user into what ORACLE needs.  This will
require customization however.  Here is a link that has some code to
help you get started.

On an aside, the the reason why your date of 12/31/2020 comes up as
12/31/2020 12:00:00 AM in ORACLE is because all Date field have a time
and 12:00:00 AM is the default time that ORACLE sets when none is
specified.  The time portion of the date field is not seen by default in
ArcMap.   

For more info on this please see the section OS Date and Times at the
following page:
http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//00s500000
033000000.htm