Problem in Fetching date from Database(Sqlite)

[Lokesh Ballebahalli]

Hello,

I am working with the project where I am using 2 QDateEdit and a button.

I need to fetch data between the specified dates, first date is selected from QDateEdit and the second date is from other QdateEdit

One QDateEdit is to select fromdate and the other is to select todate.

I executed the query in Sqlite3 Managment studio,it works but when I send query through Qt problem arrives.

Code:

QString FromcalenderDate,TocalenderDate;
   FromcalenderDate=ui->dateEdit->date().toString("yyyy-MM-dd");
   qDebug() << FromcalenderDate;
   TocalenderDate=ui->dateEdit_2->date().toString("yyyy-MM-dd");
   qDebug() << TocalenderDate;
 
   if(!db.isOpen())
   {
       qDebug()<<"Failed to database open.";return;
   }
 
   QSqlQueryModel * modal=new QSqlQueryModel();
   QSqlQuery query;
 
  if(query.exec("SELECT * FROM employees WHERE BirthDate>='"+FromcalenderDate +"' AND BirthDate<='"+TocalenderDate +"' "))
 
 
 // if(query.exec("SELECT * FROM employees WHERE BirthDate BETWEEN ('"+FromcalenderDate +"'  '"+TocalenderDate +"')"))

QString FromcalenderDate,TocalenderDate;
   FromcalenderDate=ui->dateEdit->date().toString("yyyy-MM-dd");
   qDebug() << FromcalenderDate;
   TocalenderDate=ui->dateEdit_2->date().toString("yyyy-MM-dd");
   qDebug() << TocalenderDate;

   if(!db.isOpen())
   {
       qDebug()<<"Failed to database open.";return;
   }

   QSqlQueryModel * modal=new QSqlQueryModel();
   QSqlQuery query;

  if(query.exec("SELECT * FROM employees WHERE BirthDate>='"+FromcalenderDate +"' AND BirthDate<='"+TocalenderDate +"' "))


 // if(query.exec("SELECT * FROM employees WHERE BirthDate BETWEEN ('"+FromcalenderDate +"'  '"+TocalenderDate +"')"))

To copy to clipboard, switch view to plain text mode 

There is no problem with the database connection as I am getting data for below queries:

if(query.exec("SELECT * FROM employees WHERE BirthDate ='"+FromcalenderDate +"'"))
 
if(query.exec("SELECT * FROM employees"))

if(query.exec("SELECT * FROM employees WHERE BirthDate ='"+FromcalenderDate +"'"))

if(query.exec("SELECT * FROM employees"))

To copy to clipboard, switch view to plain text mode 

I think the problem is I am leaving some some special character or Is there any format to send date to database


Any kind of help is appriciable,

Thanks and Regards,
Lokesh

[anda_skoa]

Check http://doc.qt.io/qt-5/qsqlquery.html#prepare on how to properly pass variable data to an SQL query.

Cheers,
_

[Lokesh Ballebahalli]

Hello anda_skoa,

I had a look at the document before I started connecting to database.

the document provides query to insert statment for variable data not for select statment and it doesnot discuss anything about DATE.

Regards,
Lokesh

[anda_skoa]

Yes, the SQL query example is for an INSERT.

Which does not matter at all.

The documentation is for QSqlQuery:repare()
It shows how to pass values from C++ variables into an SQL statement using placeholders.

Cheers,
_

[Lesiok]

What does it mean : when I send query through Qt problem arrives ?

[jefftee]

How is the data stored in your database for the BirthDate column of the employees table? i.e. Is it one of the supported SQLITE time string formats shown below from here?

Time Strings

A time string can be in any of the following formats:

YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
DDDDDDDDDD

In formats 5 through 7, the "T" is a literal character separating the date and the time, as required by ISO-8601. Formats 8 through 10 that specify only a time assume a date of 2000-01-01. Format 11, the string 'now', is converted into the current date and time as obtained from the xCurrentTime method of the sqlite3_vfs object in use. The 'now' argument to date and time functions always returns exactly the same value for multiple invocations within the same sqlite3_step() call. Universal Coordinated Time (UTC) is used. Format 12 is the Julian day number expressed as a floating point value.

Formats 2 through 10 may be optionally followed by a timezone indicator of the form "[+-]HH:MM" or just "Z". The date and time functions use UTC or "zulu" time internally, and so the "Z" suffix is a no-op. Any non-zero "HH:MM" suffix is subtracted from the indicated date and time in order to compute zulu time. For example, all of the following time strings are equivalent:

2013-10-07 08:23:19.120
2013-10-07T08:23:19.120Z
2013-10-07 04:23:19.120-04:00
2456572.84952685

In formats 4, 7, and 10, the fractional seconds value SS.SSS can have one or more digits following the decimal point. Exactly three digits are shown in the examples because only the first three digits are significant to the result, but the input string can have fewer or more than three digits and the date/time functions will still operate correctly. Similarly, format 12 is shown with 10 significant digits, but the date/time functions will really accept as many or as few digits as are necessary to represent the Julian day number.

I see that you are formatting the values from your QLineEdit's into yyyy-MM-dd format, so hopefully your database field is in the same format. As I'm sure you know, SQLITE is not strongly typed, so there will be no conversion for literal comparisons, so your sql comparison operations need to use the same format as the data is stored in your database.

Also, unless mistaken, if you are indeed storing your BirthDate as one of the supported time string formats above, I believe SQLITE internally stores these in UTC date/time zone, so your comparisons may need to consider that or use one of the modifiers that would use your local time zone. i.e.

select * from employees where date(BirthDate,'localtime') >= '1980-01-01' and date(BirthDate,'localtime') <= '1980-12-31'

select * from employees where date(BirthDate,'localtime') >= '1980-01-01' and date(BirthDate,'localtime') <= '1980-12-31'

To copy to clipboard, switch view to plain text mode 

Just an example of course, but you should not build your sql statement using string concatnation and use prepare/bindValue as others have mentioned, especially since you are using input from your UI and you are exposing your code to SQL injections. Something like:

QString sql = "select * from employees where date(BirthDate,'localtime') >= :start and date(BirthDate,'localtime') <= :end";
QSqlQuery q(yourdb);
q.prepare(sql);
q.bindValue(":start", FromcalenderDate.toUtf8().constData());
q.bindValue(":end", TocalendarDate.toUtf8().constDate());
bool success = q.exec();

QString sql = "select * from employees where date(BirthDate,'localtime') >= :start and date(BirthDate,'localtime') <= :end";
QSqlQuery q(yourdb);
q.prepare(sql);
q.bindValue(":start", FromcalenderDate.toUtf8().constData());
q.bindValue(":end", TocalendarDate.toUtf8().constDate());
bool success = q.exec();

To copy to clipboard, switch view to plain text mode 

If you are still having problems, show the format of the data in your database for the BirthDate column and a better explanation that states exactly what isn't working. i.e. Query successful with zero results or query returns an error, etc?

[Lokesh Ballebahalli]

Hello jefftee,

Thank you for the replay,it seems to work now.

Regards,
Lokesh