In the application for kotlin for android there is a date in the format dd.mm.yyyy (text obtained from the input field), how to convert it to yyyy.mm.dd in an SQL query like "SELECT ФИО FROM users WHERE ФИО = '${fio!!.text}' AND дата between тут дата and тут дата ORDER BY DESC"

    1 answer 1

    Java option:

    Use SimpleDateFormat , for example:

     SimpleDateFormat format = new SimpleDateFormat("dd.MM.yyyy"); Date date = format.parse("12.01.1998"); SimpleDateFormat format2 = new SimpleDateFormat("yyyy-MM-dd"); String date2 = format2.format(date);

    date 2 - formatted string for SQL query

    MySQL Option:

     SELECT ФИО FROM users WHERE ФИО = '${fio!!.text}' AND дата BETWEEN STR_TO_DATE('ТУТ_ВАША_ДАТА в фомате dd.mm.yyyy', "%d.%m.%Y") AND STR_TO_DATE('ТУТ_ВАША_ДАТА', "%d.%m.%Y") ORDER BY дата DESC

    SQLITE Option:

    https://stackoverflow.com/questions/1858275/alternative-to-str-to-date-in-sqlite

    • there is no easier way? type DATE_FORMAT (date, '% d.% m.% Y') for use within the query? - Ksenia
    • @ Ksenia try STR_TO_DATE, updated the answer - Ray Smith
    • in SQLite there is no function STR_TO_DATE , since the issue of Android is Eugene Krivenja
    • @Eugene Krivenja on Stack has both “sql” and “sqlite” tags, the first one is clearly indicated in the topic - Ray Smith
    • In SQL, as such, this function is also missing; it is only in MySQL - Eugene Krivenja