Forum Discussion

MichaelFrishma1's avatar
MichaelFrishma1
Qrew Cadet
4 years ago

Min/Max Dates with Formula Queries

I need to use a formula query to find the minimum and maximum date/time for a group of records (Flight Arrival Date/Time) in the same table. I know that there is currently no ability to find the min/max inside the formula query, but I'm trying to figure a way around that.

I can get the query formula field to display "09-24-2021 01:45pm; 09-24-2021 4:00pm; 09-24-2021 12:24pm..." and so on.

At the end of the day, I want a date/time field that will show me "09-24-2021 12:24pm" as the minimum (earliest) of the three date/times listed above.

If I use this formula, it gives me a single date/time, but it's not the earliest one.
Min(
Part(ToText([FormulaQuery]),1,";"),
Part(ToText([FormulaQuery]),2,";"),
Part(ToText([FormulaQuery]),3,";"),
Part(ToText([FormulaQuery]),4,";"))

I can get it to work if I use only the time portion, as that can be converted to a numeric decimal, but that won't work for my purposes since a flight may come in at a later time but an earlier date.

Doesn't seem I can take a textlist field, "Part" it, convert it to a date/time, and find the min/max.

Am I missing anything?

------------------------------
Michael Frishman
------------------------------

3 Replies

  • Try converting your text result to a date. 

    Min(
    ToDate(Part(ToText([FormulaQuery]),1,";")),
    ToDate(Part(ToText([FormulaQuery]),2,";")),
    ToDate(Part(ToText([FormulaQuery]),3,";")),
    ToDate(Part(ToText([FormulaQuery]),4,";")))



    ------------------------------
    Mark Shnier (YQC)
    [email protected]
    ------------------------------
    • MichaelFrishma1's avatar
      MichaelFrishma1
      Qrew Cadet
      Thanks for the help, but that's not working. If I keep the field as a Date/Time, then I get "Expecting datetime but found date". But if I change the field to a Date field, I lose the flight arrival time which I need to get the Min.

      ------------------------------
      Michael Frishman
      ------------------------------