issue with sqlite select qry by date & time for 24 hour period

[jefftee]

I may have got confused on this part

See the explanation of SQLITE data types here, they're essentially useless.

I have looked into sqlite date() time() functions using the sqlite doc like you mentioned. I have also read else where that you may have issue using other date formats other than YYY-MM-DD when comparing dates... however I thought maybe there was work around using qt libraries and c++

Not sure exactly where else you read that, but it's exactly what I said in my prior post.

I believe I do all my comparison in c++ so not sure if that would make a difference

Your original example shown the comparison was done in SQL using the BETWEEN operator. If your example has changed and you haven't re-posted your code, you might want to consider that or else you leave us all dazed and confused when you make statements that aren't supported by the example code you post.

so it sounds like I'm stuck with European dateTime format ? I don't think I will be able to use MM-DD-YYYY format then or use dates with times...

You're stuck with the format required by the database engine you chose, which in this case is SQLITE. Make it easy on yourself and store the date/time data in one of the supported (and documented) formats. You can read the date/time data from the database and show in any format you desire, but the key is you need to store date/time data in a format the database will recognized it as date/time data.

I'm having an issue with an archive fucntion that I just posted that is most likely related to this same issue idk though...

this is what I did to get that part to work earlier, I wanted to keep the variable data type to dateTime but was forced to change them to QStrings and it seem to work okay for selecting data from database however I have run into other issue since then:

QString beginDate = QDateTime::currentDateTime().toString("MM-dd-yyyy 00:00:00");
    QString endDate = QDateTime::currentDateTime().toString("MM-dd-yyyy 23:59:59");
 
    qDebug() << "UserEventLog::selectEvent() beginDate: " << beginDate;
    qDebug() << "UserEventLog::selectEvent() endDate: " << endDate;

QString beginDate = QDateTime::currentDateTime().toString("MM-dd-yyyy 00:00:00");
    QString endDate = QDateTime::currentDateTime().toString("MM-dd-yyyy 23:59:59");

    qDebug() << "UserEventLog::selectEvent() beginDate: " << beginDate;
    qDebug() << "UserEventLog::selectEvent() endDate: " << endDate;

To copy to clipboard, switch view to plain text mode 

I can't figure out what your new problem is, but if I had to guess, it's because you haven't stored data in one of the documented date/time formats and so SQLITE is doing pure lexical comparisons of what you want to be treated as date/time data. i.e. in your desired format, you want 10-06-2016 > 10-07-2015. Since it's not stored in a supported SQLITE date/time format, SQLITE winds up doing a comparison based on lexical order, which means that 10-06-2016 < 10-07-2015, clearly not what you expect.

The supported/recommended date/time formats like 2016-10-06 vs 2015-10-07 is 1) recognized as a date/time value and 2) lexical order comparison works as you would expect. This is *not* a coincidence.

Edit: Now I see you have started a new post with the same topic... sigh...