Using a query with a datetime cell in spreadsheets

Question

I need to query a cell that contains a datetime but its formatted by default when I import it to only show the date.

31/7/2020 19:18:58 (in reality it's like this)

31/7/2020 (but it shows this)

So when I run this query:

=QUERY(A5:R10, "select K")

It returns only the date no matter what I do:

31/07/2020

I've tried:

• Options
• Formatted like "yyyy-MM-dd HH:mm" it returns 00:00
• Filter by datetime or timestamp
• Used '"&Text(now(), "yyyy-mm-dd HH:mm"&"' or something like that

The question is:

Is there a way to do what I'm trying to do without reformatting the imported cells?

link to test it: https://docs.google.com/spreadsheets/d/14rGPngGvFXGP8txMS2yFo1v4gPcI4K1oYWj_8yt2Uq8/edit?usp=sharing

when I select one cell with F2 it shows the time:

Thanks a lot for your time!

Answer 1

select column B and format it as date time

---

or without reformating it:

=ARRAYFORMULA(QUERY(1*(B2:B4&""), "format Col1 'yyyy-mm-dd hh:mm:ss'"))

or:

=ARRAYFORMULA(QUERY(TEXT(B2:B4, "yyy-mm-dd hh:mm:ss"), "select *"))

Answer 2

Just make sure your cells are formatted as Automatic

If not, even if you use the format clause of the query, the clause will be ignored