Adjust display of date-time in Base queries?

[mrodent]

I’m now using LO Base quite happily as my front-end to MariaDB, after getting on top of the macro language (using Python) so that forms became practical to use.

But in the left-hand column there’s an icon with “Queries” under it letting you also do quick-and-dirty displays of random queries. I’ve got a SELECT query there with a DATETIME column. Unfortunately it displays like this: dd/mm/yy hh:mm. But I want it to display like this: yyyy-mm-dd hh:mm, i.e. an ISO-type display.

When I run the same query in phpMyAdmin, for example, it displays like I want it to. I briefly wondered whether a CONVERT( … ) command in the SQL might do the job, but every attempt gives “you have an error in your SQL”.

Is there some setting which controls the default display format of DATEs and DATETIMEs in LO Base? And is there some way to change it? Maybe some “LOCALE” setting or something?

[RobertG]

Formatting won’t be saved for queries. But if GUI will see the right field of a table for the content in a query it will show the formatting of the data source (table). So: Switch to the field of the table. Right mouse click on the table header for the DATETIME-column. Set the formatting you need.

Since some versions there is a bug, which couldn’t be reproduced. Sometimes this format will be lost after closing and reopening the Base file.

[cpb]

OR
you could use the function TO_CHAR.
formats a date or timestamp type, returns a string.
this returns a string(text) formatted as desired from a timestamp type field called MyDateField.

select
	t.*,
	to_char(`MyDateField`, 'YYYY-MM-DD HH24:MM') ts_as_string
from my_table t