Split french date issue

[Rémy_Gourrat]

Hi,

In ArcGis Pro 2.9.5 with Calculate Fied, i split a french date text and i want insert in Date Field.

First task, split the text with / to extract the 3 parts of the text

var date_array = Split("01/01/1987","/",-1);
return IndexOf(date_array,0)  + "/" + IndexOf(date_array,1)  + "/" + IndexOf(date_array,2) 

Returned "-1/-1/-1"

i don't see my mistake

Thanks forn your help

Rémy

[DanPatterson]

could it be that Date expects year, month, day?

Date Functions | ArcGIS Arcade | ArcGIS Developers

[JohannesLindner]

var date_array = Split("01/01/1987","/",-1);

// Date(year, month day)
// month is zero indexed -> January is 0
return Date(date_array[2], date_array[1] - 1, date_array[0])

[JohannesLindner]

Here's your error:

IndexOf searches for an element in an array and returns its index. It returns -1 if the element is not in the list. So you searched for 0, 1, and 2 in ["1", "1", "1987"]. These elements are not in the array, so IndexOf returns -1.

To get a certain element of an array by index, use bracket notation:

var test_array = [1, 2, 3]
Console(test_array[0]) // 1
Console(test_array[1]) // 2
Console(test_array[2]) // 3
Console(test_array[-1]) // 3
Console(test_array[-2]) // 2
Console(test_array[-3]) // 1
Console(test_array[3]) // index error
Console(test_array[-4]) // index error

[Rémy_Gourrat]

Thanks for your answereds,

@DanPatterson and @JohannesLindner, i'm agree with you but my issue was before like @JohannesLindner has detected.

@JohannesLindner i recognize the webmap expression editor, but my issue was under ArcGIS Pro 2.9.5. 

I retype the code and it's better but not perfect...

first point :

Date substract 1 hour to my date, i tried with ToLocal or with setting hours and minutes to 0, but it's same thing. I can add 1 hour but i would like understand...

second point :

In ArcGIS Pro 2.9.5, when you valid my arcade expression i have an error message but when i apply it it's running well ! may be a bug ?

Rémy

[JohannesLindner · Accepted solution]

First Point

I didn't think about timezones when I tested... The feature class stores date values in UTC. According to the documentation, the value returned by Date() is considered to be UTC, but the behavior you (and me, too) see suggests that it treats it as local time. You're in CET (UTC+1), so it subtracts the hour. In the summer months, you're in CEST (UTC+2), you should see values with a time of 22:00:00.

I haven't found a way to change that behavior. ToUTC() and ToLocal() don't have any effect. Setting the hour to 1 (or adding an hour) works, but that still leaves the problem with summer time.

I think in this case it's best to do it with Python. In the Field Calculation, switch to Python and use this expression:

datetime.datetime.strptime(!zz_ff_jdatat!, "%d/%m/%Y")

Second Point

This seems to be a bug. For validation, ArcGIS runs the expression on a row of your table. It apparently doesn't honor definition queries of the layer. You probably have a definition query set to "TextField IS NOT NULL" or something similar. When the tool runs, it doesn't encounter an error, but for validation, it grabs a feature that should be excluded from the layer.

I just posted an idea about this, please lend your support to it, so that this will hopefully be fixed in the future: Honor Definition Query in Arcade Validation - Esri Community

[Rémy_Gourrat]

@JohannesLindner 

Finally for the second point, ArcGIS Pro don't validate my expression but execute it, i just change my code like this to be more robust...

var date_array;
if ($feature.zz_ff_jdatat != Null) {
   date_array = Split($feature.zz_ff_jdatat,"/",-1);
   return Date(date_array[2],date_array[1]-1,date_array[0],0,0);
}
else {
   return Null;
}

Thanks for your help