Android Question Change Date format in SQLiteLight4 example - solved!

[Didier9]

I am using the example SQLiteLight4 for inspiration for a project and this example displays the date as yyyy-mm-dd in the web table.
I would like to display dates as dd-MMM-yyyy but I have not been able to find where that is defined, or maybe it is not defined and just an html/css default?
Anyhow, any suggestion for how to change it would be appreciated.
PS: like with most other examples, I have been learning a lot of stuff just trying to understand and use this example for my project. Thank you!

 

[klaus]

The title has nothing to do with DBUtils.
The SQLiteLight4 project doesn't use DBUtils.
The date format is a limitation with the SQLite date() function.

This is the query:
Query = "SELECT Stock.Number, Products.Name AS Product, Suppliers.Name AS Supplier, Products.Price AS Price, Stock.Number * Products.Price AS Value, date(Stock.Date / 1000, 'unixepoch') AS Date"

And the date() function: date(Stock.Date / 1000, 'unixepoch') AS Date

The date is stored as B4A ticks in the database, in Stock.Date, which are the number of milliseconds since 1970.
The date() function admits unixepoch date strings which are the number of seconds since 1970.
That's the reason why Stock.Date is divided by 1000.

If you use another table to display the data you could read the ticks, the content of Stock.Date, and transform it to your date format when you fill the table.

If you define your own database you can save the date directly in another format in the database instead of saving ticks.

 

[Didier9]

The title has nothing to do with DBUtils.
The SQLiteLight4 project doesn't use DBUtils.
The date format is a limitation with the SQLite date() function.

This comment is right into the module that sends the SQL data to the webview (Utils.bas from the SQLiteLight4 example):

'This routine is extracted from the DBUtils code module
'Creates a html text that displays the data in a table.
'The style of the table can be changed by modifying HtmlCSS variable.

Since that was a significant piece of information, I mentioned it in the post. If you still believe this piece of code has nothing to do with DBUtils, the SQLiteLight4 example is wrong and should be corrected.

If you use another table to display the data you could read the ticks, the content of Stock.Date, and transform it to your date format when you fill the table.
If you define your own database you can save the date directly in another format in the database instead of saving ticks.

I have done that, i.e. I know how to display the date how I want it from within B4A, but I have not found out how to change the way it is displayed in the webview. As you seem to indicate, the conversion happens somewhere in the SQLite engine behind the curtain.

 

[Didier9]

If you define your own database you can save the date directly in another format in the database instead of saving ticks.

On second read, that seems the simplest, but since the database will be sorted on date, I am not sure how that's going to work.
Some testing is in order.
Thanks for the suggestion.

 

[klaus]

'This routine is extracted from the DBUtils code module

Yes, the routine is extracted from the DBUtils module, but SQLitiLight4 project doesn't use the DBUtils module and the title can be confusing as your problem is not a DBUtils problem.

I was rethinking about your question.
The table is filled in the ExecuteHtml routine in the Utils module.

You can modify the query:
Query = "SELECT Stock.Number, Products.Name AS Product, Suppliers.Name AS Supplier, Products.Price AS Price, Stock.Number * Products.Price AS Value, date(Stock.Date / 1000, 'unixepoch') AS Date"
to
Query = "SELECT Stock.Number, Products.Name AS Product, Suppliers.Name AS Supplier, Products.Price AS Price, Stock.Number * Products.Price AS Value, Stock.Date AS Date"

And modify line 25 in the ExecuteHtml routine in the Utils module:
sb.Append("<th>").Append(cur.GetColumnName(i)).Append("</th>")
to
If cur.GetColumnName(i) = "Date" Then
sb.Append(".com'>").Append(DateTime.Date(cur.GetDouble2(i))).Append("</a>")
Else
sb.Append(".com'>").Append(cur.GetString2(i)).Append("</a>")
End If
And modify in the Filter module all the other code where date(Stock.Date / 1000, 'unixepoch') AS Date is used.

 

[Didier9]

Yes, the routine is extracted from the DBUtils module, but SQLitiLight4 project doesn't use the DBUtils module and the title can be confusing as your problem is not a DBUtils problem.

I was rethinking about your question.
The table is filled in the ExecuteHtml routine in the Utils module.

You can modify the query:
Query = "SELECT Stock.Number, Products.Name AS Product, Suppliers.Name AS Supplier, Products.Price AS Price, Stock.Number * Products.Price AS Value, date(Stock.Date / 1000, 'unixepoch') AS Date"
to
Query = "SELECT Stock.Number, Products.Name AS Product, Suppliers.Name AS Supplier, Products.Price AS Price, Stock.Number * Products.Price AS Value, Stock.Date AS Date"

And modify line 25 in the ExecuteHtml routine in the Utils module:
sb.Append("<th>").Append(cur.GetColumnName(i)).Append("</th>")
to
If cur.GetColumnName(i) = "Date" Then
sb.Append(".com'>").Append(DateTime.Date(cur.GetDouble2(i))).Append("</a>")
Else
sb.Append(".com'>").Append(cur.GetString2(i)).Append("</a>")
End If
And modify in the Filter module all the other code where date(Stock.Date / 1000, 'unixepoch') AS Date is used.

Great!

There were a couple of simple errors in the Utils mods, but it got me on the right track in a hurry, thanks!

It was not line 25 but line 41.
I added "DateTime.DateFormat = "dd-MMM-yyyy"" on line 29
I changed the GetDouble2() to GetLong2()
and bingo!
Thanks a lot!
PS: I changed the title to better reflect the issue, and I had already changed the other places where the date was used, the webview was the last one.
PPS: I cannot believe how easy B4A is. In just a few hours (and a question on this forum , I was able to create a brand new app (Expense tracking for small business, with easy output for Schedule C filing) using the SQLiteLight4 example.