I'm facing a problem while running a query which retrieves data from database for a given period of time.
The idea is that the user chooses 2 different dates (from , to) and then a ListView is populated with the data retrieved from a SQLite database - this data being between the from and to dates.
The query works perfectly without the WHERE clause, but when I try setting the date range, it fails.
I have stored the dates in SQLite in this format M/d/yyyy 12:00:00 AM.
While I send the dates (parameters) to the SELECT statement in this format yyyy-MM-dd.
I don't know how to deal with this.
Here is my code:
public class ProductionCommentsActivity extends Activity implements View.OnClickListener {
private DBHandler dbHandler;
private ListView listView;
private Context context;
private ArrayList<String> results = new ArrayList<String>();
private ArrayAdapter adapter;
private static String newline = System.getProperty("line.separator");
private EditText editTextFrom, editTextTo;
private DatePickerDialog datePickerDialogFrom, datePickerDialogTo;
private SimpleDateFormat simpleDateFormat;
private String fromDate,toDate ; // variables to store the chosen dates
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_production_comments);
dbHandler = new DBHandler(this, dbHandler.DATABASE_NAME_PRODUCTION, null, 1);
try {
dbHandler.copyDataBase();
Log.d("copydb", dbHandler.getDatabaseName());
} catch (IOException e) {
e.printStackTrace();
Log.d("copydb",e.getMessage());
}
//defining list view
listView = (ListView) findViewById(R.id.listView);
//defining edit texts properties
editTextFrom = (EditText) findViewById(R.id.editTextFrom);
editTextFrom.setInputType(InputType.TYPE_NULL);
editTextFrom.requestFocus();
editTextTo = (EditText) findViewById(R.id.editTextTo);
editTextTo.setInputType(InputType.TYPE_NULL);
//setting up the date format
simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd", Locale.US);
setDateTimeField();
context = this;
}
//method to handle the date pickers properties
private void setDateTimeField() {
editTextFrom.setOnClickListener((View.OnClickListener) ProductionCommentsActivity.this);
editTextTo.setOnClickListener((View.OnClickListener) ProductionCommentsActivity.this);
//creating a new instance of the calendar
Calendar newCalendar = Calendar.getInstance();
//creating a pop up date picker
datePickerDialogFrom = new DatePickerDialog(this, new DatePickerDialog.OnDateSetListener() {
//getting the chosen date and setting its format
//and writing the chosen date in the edit text
public void onDateSet(DatePicker view, int year, int monthOfYear, int dayOfMonth) {
Calendar newDate = Calendar.getInstance();
newDate.set(year, monthOfYear, dayOfMonth);
editTextFrom.setText(simpleDateFormat.format(newDate.getTime()));
fromDate = editTextFrom.getText().toString();
}
},newCalendar.get(Calendar.YEAR), newCalendar.get(Calendar.MONTH),
newCalendar.get(Calendar.DAY_OF_MONTH));
datePickerDialogTo = new DatePickerDialog(this, new DatePickerDialog.OnDateSetListener() {
public void onDateSet(DatePicker view, int year, int monthOfYear, int dayOfMonth) {
Calendar newDate = Calendar.getInstance();
newDate.set(year, monthOfYear, dayOfMonth);
editTextTo.setText(simpleDateFormat.format(newDate.getTime()));
toDate = editTextTo.getText().toString();
}
},newCalendar.get(Calendar.YEAR), newCalendar.get(Calendar.MONTH),
newCalendar.get(Calendar.DAY_OF_MONTH));
}
// on click method to handle which edit text was touched
// and show the appropriate pop up calendar
@Override
public void onClick(View view) {
if(view == editTextFrom) {
datePickerDialogFrom.show();
} else if(view == editTextTo) {
datePickerDialogTo.show();
}
}
public void searchDates(View view){
setDateTimeField();
try {
// getProductionComments(fromDate, toDate);
getProductionComments(fromDate,toDate);
adapter = new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1, results);
listView.setAdapter(adapter);
}catch(Exception ex){
Toast.makeText(this, ex.getMessage(), Toast.LENGTH_LONG).show();
}
}
//get production comments data
private void getProductionComments(String from, String to) {
try {
SQLiteDatabase db = dbHandler.getReadableDatabase();
String query = "SELECT Date,Item,Comments FROM ProductionCommentData WHERE Date BETWEEN "+ from +
"AND "+ to+";";
Cursor cursor = db.rawQuery(query,null);
if (cursor != null) {
if (cursor.moveToFirst()) {
do {
String date = cursor.getString(cursor.getColumnIndex("Date"));
String item = cursor.getString(cursor.getColumnIndex("Item"));
String comments = cursor.getString(cursor.getColumnIndex("Comments"));
results.add("Date: " + date.substring(0, 9) + newline + newline +
"Item: " + item + newline + newline + "Comments: " + comments);
} while (cursor.moveToNext());
}
}
} catch (SQLiteException se){
Log.e(getClass().getSimpleName(), "Error retrieving data from database");
}
}
