Creating a list of unique dates

Question

Hi, I am trying to create a list item that just shows the unique dates from a "StartDateTime" field in my layer. I have been able to do this if I reformat the date field to a string field but then the dates in the list are sorted numerically rather than in date order (e.g. all the 1st of each month at the top of the list etc).

My date field however is a date/time field and I'm struggling to work out how to remove the time part so I can just group by/show the unique dates.

I've so far tried this by creating a data expression, with the basic expression below. If anyone could provide help on how to edit this so it only brings back the unique dates (excluding the times) I would be very grateful. Or if there is some other way to do this e.g. through Advance formatting?

var fs = FeatureSetByPortalItem(Portal('https://xyzx.arcgis.com/'), 'xyzxyzx', 0, ['StartDateTime'], false);

return Distinct(fs,['StartDateTime']);

kenbuja · Answer

You can use a SQL expression in the Distinct function

var fs = FeatureSetByPortalItem(Portal('https://xyzx.arcgis.com/'), 'xyzxyzx', 0, ['StartDateTime'], false);
return Distinct(fs,{
  name: "StartDateTime",
  expression: "CAST(StartDateTime as DATE)"
})

However, if your dates are in the Western Hemisphere, then you have to add the correct number of hours to correct for the UTC offset.

AustinAverill · Answer

If all you are needing is the date values, you can read each date value and parse it into its individual parts then add those as a new date item to an array. Then take the distinct values from that array.

var fs = FeatureSetByPortalItem();

var dates = [];
var month;
var day;
var year;

for(var f in fs){
  month = Month(f['StartDateTime']);
  day = Day(f['StartDateTime']);
  year = Year(f['StartDateTime']);
  push(dates, Date(year, month, date))
}

return Distinct(dates)