Confused sorting on SQLLite dates

Discussion in 'Questions (Windows Mobile)' started by mikewhite, Aug 17, 2008.

  1. mikewhite

    mikewhite Member Licensed User

    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
     
  2. digitaldon37

    digitaldon37 Active Member Licensed User

    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.
     
  3. mikewhite

    mikewhite Member Licensed User

    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
     
  4. Erel

    Erel Administrator Staff Member Licensed User

    Try:
    Code:
    SELECT * FROM orders ORDER BY [B]datetime[/B](orderdate)
    You can also change Basic4ppc date and time format:
    Code:
    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
     
  5. mikewhite

    mikewhite Member Licensed User

    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
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice