Confused sorting on SQLLite dates

[mikewhite]

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

[digitaldon37]

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.

[mikewhite]

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

[Erel]

Try:

SELECT * FROM orders ORDER BY <b>datetime</b>(orderdate)

You can also change Basic4ppc date and time format:

Sub App_Start
    DateFormat("yyyy-mm-dd")
    TimeFormat("hh:mm:ss")
    t = "1999-07-03 12:15:13"
    dateTicks = DateParse(SubString(t,0,10))
    timeTicks = TimeParse(SubString(t,11,8))
    Msgbox("Date: " & Date(dateTicks) & crlf & _ 
        "Time: " & Time(timeTicks))
End Sub

[mikewhite]

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