There is a table in the database that contains a set of some records, one of the fields in the row of this table is of type text and contains a date in this format:

SimpleDateFormat dataFromat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); cvHeader.put(dbConnector.ORDER_DATE,dataFromat.format(currentDate));

Now I want to select data from this table. The logic is as follows: there is a field in the activation with the choice of the date when the user selects it, then the variables are entered for the year, month and day, respectively, here is an example:

 private static Date changeCalendarDay(int daysCount, int field) { GregorianCalendar calendar = new GregorianCalendar(); calendar.set(year,month,day); calendar.add(field,daysCount); year = calendar.get(GregorianCalendar.YEAR); month = calendar.get(GregorianCalendar.MONTH); day = calendar.get(GregorianCalendar.DATE); return calendar.getTime(); }

This method jerks when the user increases or decreases the current date by 1. So, the user changed the date, recorded values ​​in the variables, then the method that fetches data from the database is called and displays the result in a log. But I can not select records from the database, I do this:

 dbConnector dbConnector = new dbConnector(getContext(),BasicSettings.Main_DB_Name,BasicSettings.Current_DB_Version); db = dbConnector.getWritableDatabase(); String targetDate = "'"+year + "-"+(month)+"-"+day+"'"; Cursor cursor = db.query(BasicSettings.OrderHeaders_DB_Table, dbConnector.ALL_ORDER_HEADERS_COLS_LIST, dbConnector.ORDER_DATE + "=?", new String[]{targetDate}, null, null, null); if (cursor != null) { if(cursor.moveToFirst()) { do { Log.d("ORDERSINFO",cursor.getString(cursor.getColumnIndex(dbConnector.ORDER_CODE))); } while (cursor.moveToNext()); } }

But the records do not find ... Is it possible to somehow make a sample, so as not to alter the way the date is stored in the database? How to do right?

    2 answers 2

    Once you have decided to store the date as a string in the format "yyyy-MM-dd hh: mm: ss", then search for it accordingly.
    A simple comparison is not enough; from the user you get only the date itself, and in the database it is recorded as date-time. Therefore, it finds nothing.

    For everything to work, use the LIKE operator, the query must have a condition like this:

     WHERE ORDER_DATE LIKE '2017-08-30%'

    Get all entries with a date starting on such a string, i.e. a certain day.

    There is a tutorial: http://www.sqlitetutorial.net/sqlite-like/

    • Thank! That's always the case, stuck on the most obvious) - Alexey Vladimirovich

    Selection of the form dbConnector.ORDER_DATE + "=?" will search for a full match. Since your date is stored in the yyyy-MM-dd hh:mm:ss format (with hours, seconds, and milliseconds), the string is

      String targetDate = "'"+year + "-"+(month)+"-"+day+"'";

    This select does not find.

    One solution to the problem is to use the SQL LIKE statement:

     Cursor cursor = db.query(BasicSettings.OrderHeaders_DB_Table, dbConnector.ALL_ORDER_HEADERS_COLS_LIST, dbConnector.ORDER_DATE + " LIKE ?", new String[]{"%" + targetDate + "%"}, null, null, null);

    More about the operator LIKE