nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Arcade Filter by Date

KevinBrown10

Hello,

I wish to utilize the Filter function in Arcade to only return results between 1/1/2019 and 12/31/2019. I understand the Filter function is Filter(featurerSet, filter) where filter is an SQL text string. The features are stored with a Date/Time field. I am just not understanding the proper SQL commands for working with Date/Time.

Any help or direction to more resources is appreciated.

I tagged you Xander Bakker‌ because I (a non-programmer) have learned much about Arcade following your numerous examples and help to others on GeoNet.

Thanks.

Kevin

Find more posts tagged with

Accepted answers

XanderBakker

Hi k.brown ;

It seems that the expression below works (the same format as you would use in Desktop):

<SPAN class="keyword token">var</SPAN> sql <SPAN class="operator token">=</SPAN> <SPAN class="string token">"LASTUPDATE BETWEEN date '01/01/2019' and date '12/31/2019'"</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">var</SPAN> fs <SPAN class="operator token">=</SPAN> <SPAN class="token function">Filter</SPAN><SPAN class="punctuation token">(</SPAN>$layer<SPAN class="punctuation token">,</SPAN> sql<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">return</SPAN> <SPAN class="token function">Count</SPAN><SPAN class="punctuation token">(</SPAN>fs<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

All comments

XanderBakker

Hi k.brown ;

It seems that the expression below works (the same format as you would use in Desktop):

<SPAN class="keyword token">var</SPAN> sql <SPAN class="operator token">=</SPAN> <SPAN class="string token">"LASTUPDATE BETWEEN date '01/01/2019' and date '12/31/2019'"</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">var</SPAN> fs <SPAN class="operator token">=</SPAN> <SPAN class="token function">Filter</SPAN><SPAN class="punctuation token">(</SPAN>$layer<SPAN class="punctuation token">,</SPAN> sql<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">return</SPAN> <SPAN class="token function">Count</SPAN><SPAN class="punctuation token">(</SPAN>fs<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

KevinBrown10

Perfect! This worked for me.

Thank you very much for your quick response. As I stated, I do not have much coding experience but following along with your examples is very helpful.

Much appreciated.

XanderBakker

Hi https://community.esri.com/people/k.brown,

I'm glad it worked!

nanayawosei

I am trying to implement a similar query with arcade and sql, but I can not seem to get it to work when I include variables for my dates:

var myDate1 = '01/01/2019'

var myDate2 = '12/31/2019'

var sql = "LASTUPDATE BETWEEN date 'myDate1' and date 'MyDate2'";

var fs = Filter($layer, sql);

return Count(fs);‍‍‍

Thanks ,

Nana

TomNeer

Nana,

You probably figured this out but use the @ symbol in front of your variables to call them inline.

var sql = "LASTUPDATE BETWEEN @myDate1 and @MyDate2";

TLongSUGF

I am using this same filter() in Pro 3.0 with a service published to Server/Federated 10.9.1. My arcade expression works fine in Pro, but it fails in Field Maps and Web AppBuilder with the error on the filter() - Line 4: "Failed to query statistics". Are there any limitations to this, I wouldn't think so.

My Holidays table is just a table of holidays, querying of the date column.

var t30 = dateadd($feature.initialreport,42,'days')
var t = dateadd($feature.initialreport,0,'days')
//query my date field - dt - on my holidays table
var sqls = "dt BETWEEN @t and @t30";
var fs = count(Filter(FeatureSetbyName($datastore, "SUDOECMS.DBO.Holiday"), sqls))
var dayz = dateadd($feature.initialreport,fs + 42,'days')
return dayz;

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.