topic How to calculate date difference using Current_Date in ArcGIS Online Questions

How to calculate date difference using Current_Date

TomRippetoe — Thu, 03 Nov 2016 18:14:10 GMT

Hello.

I am working with an AGOL hosted feature layer. I am trying to calc the difference between when a record was added and the current date.

I am using the 'calculate' function that is available by clicking in the 'gear' icon that appears in the upper right corner of a column header.

The expression i am using is 'AgeInDays = CURRENT_DATE() - DateReceived'. That expression successfully validates.

When i click the 'Calculate' button, i get the following error message:

Calculate for the expression 'CURRENT_DATE() - DateReceived' failed.
Implicit conversion from data type datetime to float is not allowed. Use the CONVERT function to run this query.

I have tried various forms of the CONVERT function as suggested by the error message, but i haven't stumbled upon the correct syntax.

Any idea why that expression is not working. My reading of the 'working with fields' documentation suggests that the number of days should be returned. In fact when i use two date fields from the table in the expression (rather than CURRENT_DATE), the number of days is returned.

Is there something special i need to do to use 'CURRENT_DATE'?

Any suggestions on how to use CURRENT_DATE to get the difference between two dates?

Thank you.

Re: How to calculate date difference using Current_Date

ChrisWhitmore — Fri, 04 Nov 2016 16:12:51 GMT

Hi Tom,

This looks like a bug. It's specific to using the CURRENT_DATE (or _TIME, _TIMESTAMP) function. To use the current date, you'll need to use a date literal, like DATE'11/04/2016' or DATE'11/04/2016 5:00PM'. Edit: you could also create a dummy date field and calculate the field to use CURRENT_DATE and then perform the calculation on your number field using the date field storing CURRENT_DATE (the dummy field could be removed after).

The bug has been logged internally and expected to be fixed in the next ArcGIS Online release (currently planned for December). Feel free to contact Tech Support if you'd like a public facing bug logged.

Thanks,

Chris

ArcGIS Online team

Re: How to calculate date difference using Current_Date

TomRippetoe — Fri, 04 Nov 2016 17:04:04 GMT

Thanks for the info Chris.