...ORDER BY dateField

Databases
1

sSQL = "CREATE TABLE Events(" sSQL = sSQL + "pkRecID INTEGER PRIMARY KEY, " sSQL = sSQL + "EventDate DATE, " sSQL = sSQL + "Type VARCHAR, "

sSQL = "SELECT * FROM Events ORDER BY EventDate" rsRecall = dbSQL.SQLSelect(sSQL)

I have a SQLite table with a field defined as DATE - Top code above.
Then I retrieve the data and try to sort it by the EventDate. It’s then loaded into a ListBox - lower code.
The records are sorted by the 1st digit of the date instead of the full value of the date.

How do you order a select by a date field?

thanks,

2

Define the date in the Sqlite database as a string.
Then use the myEventDate.SqlDate format to store the date into the Events table.

You can then use your select statement as written to order the EventDates.

To retrieve the date, define a date and move the retrieved sqldate string into it
Dim myEventDate as Date = EventDate.sqldate

Jim

3

How do you store the dates? DatabaseRecord/RecordSet.Update? Sql statements formed in code? PreparedStatements?

If you’re constructing sql in code, always use SQLDate as the value. Never ShortDate or any other format.

ANSI standard date format is YYYY-MM-DD.

4

What you are doing should already work.

It is already sorted, databases know how to do that very well (don’t save dates as strings!), you probably destroy the sort order by mistake somehow. Show some code…