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";
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?
Bookmarks