How to fetch Data Between Two Dates ?
-
I wrote this code on how to fetch data between two dates ( FROM date to To date).?
QDate fdate= ui->dateEdit_4->date();
QString fdateString = fdate.toString("dd / MM / yyyy");
QDate tdate= ui->dateEdit_5->date();
QString tdateString = tdate.toString("dd / MM / yyyy");
QSqlQueryModel * modal = new QSqlQueryModel();
QSqlQuery* query=new QSqlQuery(mydb);
query->prepare("SELECT PID , TName , ANrmlRang , BNrmlRang , ODVal , ResultOD , Date , Time FROM Reports WHERE Date BETWEEN '"+fdateString+"' AND '"+tdateString+"'"); -
Hi,
You must have date field in your data and when you are querying you can
fetch data by "where" expression if you are using SQL@ali-aydin
The OP shows aDatefield, and uses it in aWHEREclause already. I don't think that in itself is any issue. -
I wrote this code on how to fetch data between two dates ( FROM date to To date).?
QDate fdate= ui->dateEdit_4->date();
QString fdateString = fdate.toString("dd / MM / yyyy");
QDate tdate= ui->dateEdit_5->date();
QString tdateString = tdate.toString("dd / MM / yyyy");
QSqlQueryModel * modal = new QSqlQueryModel();
QSqlQuery* query=new QSqlQuery(mydb);
query->prepare("SELECT PID , TName , ANrmlRang , BNrmlRang , ODVal , ResultOD , Date , Time FROM Reports WHERE Date BETWEEN '"+fdateString+"' AND '"+tdateString+"'");@Ramkumar-Mohan
couple of thing to note, first,you're supposed to bindValue() to bind to placeholders/values in your query. Construction the query in place is error prone and unreadable.
- AFAIK the query is supposed to end on a
;not sure if it's mandatory or not. - dd / MM / yyyy is not the default format for dates in SQL, that would be YYYY-MM-DD are you sure your format is correct, and not simply 2 strings that are compared ?
- if the type of your db is actually DATE , than use QDate that one is actually mapped to DATE. https://doc.qt.io/qt-6/sql-types.html
- when you execute the query and it returns false, what does https://doc.qt.io/qt-6/qsqlquery.html#lastError actually tell you ?
Be aware of the Qt Code of Conduct, when posting : https://forum.qt.io/topic/113070/qt-code-of-conduct
Q: What's that?
A: It's blue light.
Q: What does it do?
A: It turns blue. - AFAIK the query is supposed to end on a
-
@Ramkumar-Mohan
couple of thing to note, first,you're supposed to bindValue() to bind to placeholders/values in your query. Construction the query in place is error prone and unreadable.
- AFAIK the query is supposed to end on a
;not sure if it's mandatory or not. - dd / MM / yyyy is not the default format for dates in SQL, that would be YYYY-MM-DD are you sure your format is correct, and not simply 2 strings that are compared ?
- if the type of your db is actually DATE , than use QDate that one is actually mapped to DATE. https://doc.qt.io/qt-6/sql-types.html
- when you execute the query and it returns false, what does https://doc.qt.io/qt-6/qsqlquery.html#lastError actually tell you ?
@J-Hilk said in How to fetch Data Between Two Dates ?:
when you execute the query and it returns false
But that was my point: what does the user expect, it shows a
prepare()and noexec()so nothing is going to happen.... - AFAIK the query is supposed to end on a
-
@Ramkumar-Mohan
And? Please actually ask a question! What happened? Did you get an error? Did it not behave as you expected? In what way, what did you see or not see?What do you actually expect to happen from this code as shown...?
-
As shown in this photo, if I check (From date to To date), I want to find the data within those days. I wrote the above code and it didn't work for me.
I wrote the above code and it didn't work for me.
The above code does not execute any SQL query. So in itself it won't do anything, like I said. I don't know whether you have other code to execute it, because you don't say; if you do, why not say so, we cannot guess?
For the rest start with @J-Hilk's comments.
-
I wrote the above code and it didn't work for me.
The above code does not execute any SQL query. So in itself it won't do anything, like I said. I don't know whether you have other code to execute it, because you don't say; if you do, why not say so, we cannot guess?
For the rest start with @J-Hilk's comments.
-
@Ramkumar-Mohan
OK, at least now I know. You might be surprised --- we have indeed had to people who prepare queries but never execute them!You don't check the return result from the
exec(). You don't tell me whether it returns any rows. If I am to guess, are you saying "it does return rows, but it returns too many rows, it returns rows outside of my desired date range"? That is what I need to hear!So like I said start with @J-Hilk's observations. Above all, how do we know whether
BETWEEN '03 / 03 / 2022' AND '05 / 06 / 2022'is even acceptable to your SQL implementation?
Print out exactly what the query string is. Try it in whatever command-line tool you have for sending queries to your database. Try it with a plain
Date = ...instead ofBETWEENto test the format if you wonder whetherBETWEENworks. And/or use a bound variable to pass aQDatevalue instead of trying to construct a literal string for it. -
@Ramkumar-Mohan
OK, at least now I know. You might be surprised --- we have indeed had to people who prepare queries but never execute them!You don't check the return result from the
exec(). You don't tell me whether it returns any rows. If I am to guess, are you saying "it does return rows, but it returns too many rows, it returns rows outside of my desired date range"? That is what I need to hear!So like I said start with @J-Hilk's observations. Above all, how do we know whether
BETWEEN '03 / 03 / 2022' AND '05 / 06 / 2022'is even acceptable to your SQL implementation?
Print out exactly what the query string is. Try it in whatever command-line tool you have for sending queries to your database. Try it with a plain
Date = ...instead ofBETWEENto test the format if you wonder whetherBETWEENworks. And/or use a bound variable to pass aQDatevalue instead of trying to construct a literal string for it.@JonB Ok
-
@Ramkumar-Mohan
OK, at least now I know. You might be surprised --- we have indeed had to people who prepare queries but never execute them!You don't check the return result from the
exec(). You don't tell me whether it returns any rows. If I am to guess, are you saying "it does return rows, but it returns too many rows, it returns rows outside of my desired date range"? That is what I need to hear!So like I said start with @J-Hilk's observations. Above all, how do we know whether
BETWEEN '03 / 03 / 2022' AND '05 / 06 / 2022'is even acceptable to your SQL implementation?
Print out exactly what the query string is. Try it in whatever command-line tool you have for sending queries to your database. Try it with a plain
Date = ...instead ofBETWEENto test the format if you wonder whetherBETWEENworks. And/or use a bound variable to pass aQDatevalue instead of trying to construct a literal string for it.
