In a SQLite database I have a view with a column strftime(“%d/%m”, dia) AS dm
This formats a date (yyyy-mm-dd) column “dia” as dd/mm.
In the database, view displays ok. But on Base, column displays only d, without padding 0 and following “/mm”!
Any ideas?
Thanks in advance.
LO Base 24.2.7.2
SQLite 3.4.5.1
Driver JDBC 3.49.1.0
Ubuntu 24.04.2
Have tried it like described here: SQLite strftime() Function
SELECT strftime('%d','now')
will work as described.
SELECT strftime('%d/%m','now')
will work well in Tools → SQL, but won’t work in GUI.
Checked… neither this worked here.
SELECT strftime(’%d/%m’, ‘now’) FROM “Log” (Log is the basic table on database)
shows 23 (today) in the grid.
But please take notice that real problem is direct visualisation of view “vExtrato” that correctly shows itself in Tables section of Base.
CORRECTION (sorry!)
Yes! In > Tools > SQL
SELECT * FROM vExtrato
Execute > Output of SELECT displays ok dd/mm!
So, problem is: why not in the GUI grid?
GUI doesn’t know anything about functions of SQLite. For example try CONCAT(). Will work well in MariaDB, also in HSQLDB - but not in SQLite in GUI.
But this view I created directly in the database, not via Base:
CREATE VIEW “vExtrato” AS SELECT dia, strftime("%d/%m", dia) AS dm, historico FROM Log ORDER BY dia
Should not database send resultset as a string (TEXT) without need of Base invoking (parsing) the function used in database to create the view?
The report:
Yes… will try to add images.
I can only add 1 image per post so…
upload://mPIEZ3ZFIttZM5Fkc4EMfDG92ug.png)
This is from DB Browser for SQLite:
With an ODBC driver, Base shows all 4 digits.
For a report or form, I would select the date field as is and apply number format “DD/MM”.
Yes, it worked! Not on the table column “dia” (yyyy-mm-dd), as I expected because it’s not a real date in SQLite. But yes on column “dm” of the view.
Applied User-defined DD/MM ant it worked. Tested also for the 2 digit months (I’m in Brazil but locale = US) and it works.
ODBC
My choice was JDBC because instructions for ODBC appeared to me dated, generally from years ago, and complex. It looks to me like one needs to install TWO softwares and manually edit configuration text files of them.
Mystery remains but practical problem is solved, as of course goal is Report.
Thanks to both you and RobertG.
