QSqlQuery Sqlite3 specific SELECT for datetime

[MrLibya]

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 :

And the two query didn't work !

[SGaist]

Hi,

See this stack overflow post and the following answer.

Hope it helps

[MrLibya]

@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 !

[SGaist]

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

[MrLibya]

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")));

[SGaist]

Great !

Thanks for sharing :)