How can I format dates from SQLite

I’ve made a Xojo report that shows a date field from a table from a SQLite database. I changed the width of the report field so it cuts off the time, but it still is in the format of 2017-22-05. I notice the report datefield has a popup menu that allows me to choose a Xojo date format in the report inspector, how can I change the format of the SQLite data field value on the report?

TIA

I think the strftime - function is what you are after. See: https://www.sqlite.org/lang_datefunc.html

Thank you Max, I tried putting strftime(%m-%d-%Y) in the outputFormat field on the report editor, but the output on the printer was still YYYY-MM-DD.

Back to square one! Joy of programming!

[quote=327355:@William Broyles]Thank you Max, I tried putting strftime(%m-%d-%Y) in the outputFormat field on the report editor, but the output on the printer was still YYYY-MM-DD.

Back to square one! Joy of programming![/quote]
I think the suggestion is in your SELECT function being sent to the database rather than in the report definition part.

You might have something like:

dim sql As String = "SELECT theDate FROM theTable" dim rs As RecordSEt = myDB.SQLSelect(sql) dim myDisplayDate As Date = rs.Field("theDate").DateValue
which will return a date field.

The select statement should be:

dim sql As String = "SELECT strftime('%m-%d-%Y', theDate) As theDate FROM theTable" dim rs As RecordSEt = myDB.SQLSelect(sql) dim myDisplayDate As String = rs.Field("theDate").StringValue
Now the report will not apply any formatting to myDisplayDate as it is a string value.

I’m assuming that you are using the Xojo Report capability. If so, there is also the option to put code in the BeforePrinting event of the field itself. Something like the following will take the text in SQLDate format and output it in ShortDate format.

[code]Dim dte As Date
If Me.Text = “” Then Return

dte = New Date
dte.SQLDate = me.Text
me.Text = dte.ShortDate
[/code]

Dale for the life of me I cannot figure out how this works, but it does!
Your code, dropped in the report->before print event, does exactly what I wanted. Thank you!

I was about to give up and go back to MS-Access, or Visual FoxPro because of my Xojo language retardation. Is Xojo Greek for retard?

In your initial posting, you said the date format you have in string format is ‘2017-22-05’. What my code does is simply, before printing, takes the field contents (your date string) and plugs it into the SQLDate parameter of a Date object. This sets the Date object to that date . Then the code assigns the ShortDate parameter back to the field and continues on with the printing.