Results 1 to 3 of 3

Thread: sqlite and date

  1. #1
    Join Date
    Sep 2007
    Location
    Sant'Elpidio a Mare, Italy
    Posts
    194
    Thanks
    54
    Thanked 2 Times in 2 Posts
    Qt products
    Qt3 Qt4
    Platforms
    Windows

    Default sqlite and date

    Hi everyone,

    I have to save and retrieve (by BETWEEN) dates from a SQlite database.
    Which is the best way to do it? (both operations)

    I tried with saving it as a QString in the db, but then it seems that the db is not able to operate the record as a date... date(myrecord) shows an empty string...while without date() shows the string but can't be used for comparison.
    These are just a series of situations, but anyway the question is the first one: how to save date records in a sqlite db and retrieve them with between comparison?

    PS: db record IS a DATE format and I can't use QSqlQuery::bindValue() for some reasons.

    Thank you in advance!
    --
    raccoon29

    "La mia vita finirà quando non vedrò più la gente ridere...non necessariamente alle mie battute "

  2. #2
    Join Date
    Sep 2007
    Location
    Sant'Elpidio a Mare, Italy
    Posts
    194
    Thanks
    54
    Thanked 2 Times in 2 Posts
    Qt products
    Qt3 Qt4
    Platforms
    Windows

    Default Re: sqlite and date

    Ok,pals, I solved it

    I'll not be greed and I'll post what was the problem, maybe someone else needs it...

    -Problem was that I was saving the date in the SQlite database, but when I went to use those dates for comparisons, conditions returned always 'false', and from command line, sqlite operation "date(mydate)" returned an empty string (while it should return the formatted string).

    Solution: the problem persists probably because we saved the date string in the wrong format. In order to let the database understand the date and operate with it, the string must be in the format "yyyy-MM-dd", so with QDate it would become:
    Qt Code:
    1. query.bindValue(":mydate",ui.dateEdit->date().toString("yyyy-MM-dd"));
    To copy to clipboard, switch view to plain text mode 
    finally to get a result using BETWEEN is just needed the following:
    Qt Code:
    1. SELECT * FROM mydatetable WHERE mydate BETWEEN '2008-07-22' AND '2008-07-25';
    To copy to clipboard, switch view to plain text mode 
    Here we go.

    (It would be nice if someone spends two words remembering it on sqlite docs... at least I should have economized two hours, and someone else in my situation...)

    Hope this can help
    Last edited by Raccoon29; 22nd July 2008 at 17:33.
    --
    raccoon29

    "La mia vita finirà quando non vedrò più la gente ridere...non necessariamente alle mie battute "

  3. #3
    Join Date
    Feb 2008
    Posts
    50
    Thanks
    1
    Thanked 2 Times in 2 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: sqlite and date

    Thanx, just the thing i needed.

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.