Important: Please read the Qt Code of Conduct -

QSqlQuery Sqlite3 specific SELECT for datetime

  • Hello
    I"m trying to make a specific SELECT for the QSqlQueryModel and show on the QTableView , so the problem is nothing show on the
    In the first to check that the full code is ok i test

    CartItems->setQuery(QString("SELECT * from main.sell_cash_log "));

    and it's work , it show all items , now when i use what i want :

    CartItems->setQuery(QString("SELECT * from main.sell_cash_log WHERE date('when') BETWEEN date('%1') AND date('%2')").arg(ui->fromdate->dateTime().toString("yyyy-MM-dd hh:mm:ss")).arg(ui->todate->dateTime().toString("yyyy-MM-dd hh:mm:ss")));

    and i tried

    CartItems->setQuery(QString("SELECT * from main.sell_cash_log WHERE strftime('%Y-%m-%d %H:%M:%S','when') BETWEEN '%1' AND '%2'").arg(ui->fromdate->dateTime().toString("yyyy-MM-dd hh:mm:ss")).arg(ui->todate->dateTime().toString("yyyy-MM-dd hh:mm:ss")));

    and didn't work ! ,
    i used

    qDebug()<<CartItems->query().lastQuery(); // to get the query
    "SELECT * from main.sell_cash_log WHERE date('when') BETWEEN date('2015-11-09 00:00:00') AND date('2016-11-09 00:00:00')"

    and that is my table :
    alt text

    And the two query didn't work !

  • Lifetime Qt Champion


    See this stack overflow post and the following answer.

    Hope it helps

  • @SGaist I test them all but same result , and I've edit my query , now it's makeing a sence but still not work

    CartItems->setQuery(QString("SELECT * from main.sell_cash_log WHERE strftime('%Y-%m-%d %H:%M:%S','when') BETWEEN strftime('%Y-%m-%d %H:%M:%S','%1') AND strftime('%Y-%m-%d %H:%M:%S','%2')").arg(ui->fromdate->dateTime().toString("yyyy-MM-dd hh:mm:ss")).arg(ui->todate->dateTime().toString("yyyy-MM-dd hh:mm:ss")));

    there is solution it might work , in the link u give me someone says he make it as intger YYYYMMDD here mydate >= '20090101' and mydate <= '20050505' , but it's not real solution

    and I've think in something like select all the 'when' rows and then make compare each one a head , but with large database it will take alot of time !

  • Lifetime Qt Champion

    Shouldn't you use the <= and >= operators with SQLite ?

  • The problem was by the field called when , in insert query i was useing escape string ('when') but with select not work (' ' ) so i used ( when) and it's workd :

    CartItems->setQuery(QString("SELECT * from main.sell_cash_log WHERE datetime(`when`) BETWEEN datetime('%1') AND datetime('%2')").arg(ui->fromdate->dateTime().toString("yyyy-MM-dd hh:mm:ss")).arg(ui->todate->dateTime().toString("yyyy-MM-dd hh:mm:ss")));

  • Lifetime Qt Champion

    Great !

    Thanks for sharing :)

Log in to reply