How to fetch Data Between Two Dates ?

[Ramkumar Mohan]

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

[JonB]

@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...?

[ali-aydin]

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

[JonB]

@ali-aydin
The OP shows a Date field, and uses it in a WHERE clause already. I don't think that in itself is any issue.

[J.Hilk]

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

[JonB]

@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 no exec() so nothing is going to happen....

[Ramkumar Mohan]

@JonB

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.

[JonB]

@Ramkumar-Mohan

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]

@JonB

Sorry sir, I shared my code, please find the Below Attachment

[JonB]

@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 of BETWEEN to test the format if you wonder whether BETWEEN works. And/or use a bound variable to pass a QDate value instead of trying to construct a literal string for it.

[Ramkumar Mohan]

@JonB Ok

[Ramkumar Mohan]

@JonB

Now , this code is working fine sir, Thank you.