Date format issue after upgrading from 10.1 to 10.2

[AlessioDi_Lorenzo]

Hi all,

I just upgraded ArcGIS for Server 10.1 to ArcGIS for Server 10.2.
Before the upgrade, date fields in my MapServices response were like this:

"SAMPLING_DATE" : "1/5/2011 12:00:00 AM"

now, with 10.2, they are:

"SAMPLING_DATE" : 1294185600000

All of my web applications, to filter the feature displayed on maps, performs queries sending parameters about dates, like this:

SAMPLING_DATE >= TO_DATE('01/01/2010','DD/MM/YYYY') AND SAMPLING_DATE <= TO_DATE('10/10/2013','DD/MM/YYYY')

Now similar queries do not work and the rest endpoint returns "Unable to complete operation".

How can I query date fields now in 10.2 and (better) how can I have date fields again as they were in 10.1?

Thanks in advance

[AlessioDi_Lorenzo]

I got how to query:

no more:

SAMPLING_DATE >= TO_DATE('01/01/2010','DD/MM/YYYY') AND SAMPLING_DATE <= TO_DATE('10/10/2013','DD/MM/YYYY')

but:

SAMPLING_DATE >= TIMESTAMP '01/01/2010' AND SAMPLING_DATE <= TIMESTAMP '10/10/2013'

This query works properly and I hope this helps, but I still have twoo questions:

1) I'm using date strings in DD/MM/YYYY format and I noticed this format works (the query return the correct results). How about changing the date format to MM/DD/YYYY? Is there a way to specify it in the TIMESTAMP function?

2) If I run a test query inside the ReST panel - without using the javascript web app that formats the results client side - the value of the SAMPLING_DATE field is in milliseconds. Can I apply the TIMESTAMP function in the outFields field to format the answer in something more human-readable?

Thanks

[TanuHoque]

starting at 10.2, by default you can't pass in any database specific query function while querying a layer from a service. your query must conform to the StandardizedQueries specs.

http://resources.arcgis.com/en/help/main/10.2/index.html#/About_standardized_queries/015400000641000...

1) syntax for TIMESTAMP is actually timestamp 'yyyy-mm-dd hh:mm:ss'.
http://resources.arcgis.com/en/help/main/10.2/index.html#/Supported_SQL_functions_in_ArcGIS_Server/0...

2) nope, i don't think so.

[AlessioDi_Lorenzo]

Thank you for the answer mahoque.

May you provide me with an example of a standardized query filtering data by date explicitly formatted as dd/mm/yyyy?
I'm using Oracle.

Regards


PS:
by disabling the standardized query support as explained in the documentation my TO_DATE query

SAMPLING_DATE >= TO_DATE('01/01/2010','DD/MM/YYYY') AND SAMPLING_DATE <= TO_DATE('10/10/2013','DD/MM/YYYY')

will work again?

[TanuHoque]

Oh, just realized you can use either DATE or TIMESTAMP keyword.

here is 2 examples:

SAMPLING_DATE >= DATE '2010-01-01' AND SAMPLING_DATE <= DATE '2013-10-10'

SAMPLING_DATE >= TIMESTAMP '2010-01-01 00:00:00 AM' AND SAMPLING_DATE <= TIMESTAMP '2013-10-10 00:00:00 AM'

yes, you are right, by disabling StandardizedQueries you can pass in any database specific (in your case Oracle) functions.

[MattRohlf1]

Does that mean it's possible to query using the TIMESTAMP keyword even if the data is stored in Oracle as a DATE datatype?

[TanuHoque]

I'm not sure.

When StandardizedQueries is enabled, internally a standardizedQuery-function gets converted to a database-specific-function before the modified query gets sent to the database to process.

I don't know which Oracle functions, DATE and TIMESTAMP are mapped to.

[AlessioDi_Lorenzo]

Oh, just realized you can use either DATE or TIMESTAMP keyword.

here is 2 examples:

SAMPLING_DATE >= DATE '2010-01-01' AND SAMPLING_DATE <= DATE '2013-10-10'

SAMPLING_DATE >= TIMESTAMP '2010-01-01 00:00:00 AM' AND SAMPLING_DATE <= TIMESTAMP '2013-10-10 00:00:00 AM'

yes, you are right, by disabling StandardizedQueries you can pass in any database specific (in your case Oracle) functions.

mahoque,

as I wrote in my second post, I used:

SAMPLING_DATE >= TIMESTAMP 'dd/mm/yyyy' AND SAMPLING_DATE <= TIMESTAMP 'dd/mm/yyyy'

and it worked, the results were correct.
So the default CURRENT_TIMESTAMP (or CURRENT_DATE) format in my case seems to be dd/mm/yyyy and NOT yyyy-mm-dd. My questions now are:
1) Does it depend on the browser language or on the oracle server settings or what else?
2) How can I ask for dates or timestamps using a different format? Disabling standardized queries is the only way?

Thanks

[TanuHoque]

1) I believe that depends on Oracle settings. the StandardizedQueries recommends that to make it work across all databases.
That is the main idea behind StandardizedQueries, so that the end user does not need to worry about the underlying database's flavor - the end user writes a sql query following StandardizedQueries specs, and ArcGIS Server converts that to an appropriate sql statement conforming to the underlying database's syntax etc.

2) StandardizedQueries syntax doesn't allow to specify dateformat, so you are right, the only option you are left with is to disable StandardizedQueries, if it is absolutely necessary to specify the dateformat in the query.