sqlite and date

[Raccoon29]

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]

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:

query.bindValue(":mydate",ui.dateEdit->date().toString("yyyy-MM-dd"));

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:

SELECT * FROM mydatetable WHERE mydate BETWEEN '2008-07-22' AND '2008-07-25';

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

[sadjoker]

Thanx, just the thing i needed.