Confused sorting on SQLLite dates

Hi,

I'm using the sample program (sql.sbp) to learn about SQLLite databases.
I'm getting on OK except I cannot get dates to order properly. For example, if I enter the following into the SQL query text box "SELECT * FROM orders ORDER BY OrderDate" and execute it the data is returned with the OrderDate sorted like a string, NOT a date.

How do I store dates and sort on them?

Thanks

Mike White

SQLite date/time

Have you tried converting the date from a string to date format? The SQLite web site gives some examples: SQLite Query Language: Date And Time Functions

This works for me in the demo: SELECT * FROM orders ORDER BY date(orderdate)

I hope that helps.

Thank you! That works very well.

Now though I'm confused by how to construct my insert/update statements for timestamp fields (which is what the OrderDate field is).

If I insert like so '1999-07-03 12:15:13' the data will sory by date but ignores the time field. It also returns as 1999-07-03 where I would prefer 07-03-1997 for easy use in Dateparse etc.

I seem to be making a mountain out of what must really be very simple!

Thanks

Mike White

Try:
You can also change Basic4ppc date and time format:

Thanks,

I'm on my way again. I also thought about just storing the number of ticks in a field and then decoding that via Date and time.

Mike