Select max() in Google Sheet Query on Dates gives different result to =max() formula

Question

I'm trying to return the last time log from a list in Google spreadsheet using Google Sheet query max() function.

=query(A:A, "select max(A)",1)

The time log data (column A) is in text format dd/mm/yyyy hh:mm:ss, which is imported to google sheet using importdata function.

The above query is not returning the correct result. I think it's because the source of data is in text format.

e.g. The above query will return 9/9/2017 23:58:00 while the normal =max(A:A) function returns 12/9/2017 19:12:00.

Is it possible to reformat the text to datetime format within the query clause?

The data sample is from here: https://docs.google.com/spreadsheets/d/1EwT5ZvCCLLorWomaeJFMhAziWnhW9zTwco9dfwoXJJ4/edit#gid=0

Answer 1

It's possible, for example:

=query(ArrayFormula(1*filter(A:A,NOT(ISBLANK(A:A)))), "select max(Col1)",0)

(and probably something a bit simpler!) but the obvious preferred option is to treat date/times as date/times, not text.