How Can I Fix Wrong Date Format In Sqlite

I'm working on app where I use SQLite to store data. I created column Date. Since I'm beginner I made a mistake by inputing date as %m/%d/%Y (for example: 2/20/2020) Now I've got a

Solution 1:

Update your dates to the only valid for SQLite date format which is YYYY-MM-DD:

update tablename
setdate= substr(date, -4) ||'-'|| 
           substr('00'|| (date+0), -2, 2) ||'-'||
           substr('00'|| (substr(date, instr(date, '/') +1) +0), -2, 2);

See the demo. Results:

| ID  |Date||--- | ---------- ||1|2019-09-02||2|2020-02-20|

Now you can set the conditions like:

DateBETWEEN'2019-02-05'AND'2020-02-20'

If you do this change then you can use the function strftime() in select statements to return the dates in any format that you want:

SELECT strftime('%m/%d/%Y', date) dateFROMTable

If you don't change the format of date column then every time you need to compare dates you will have to transform the value with the expression used in the UPDATE statement, and this is the worst choice that you could make.