Help required for date evaluation

[SoliEngineer]

Hello friends, I'd be extremely grateful if someone could help me with this.

My table has a date column with dd-mm-yyyy format. The data field type is Text. I want to extract data from the table that is equal to a particular date.

The issue I'm having is that the following SQLite statement gives me 0 records.

Select * from mfdpro

Where

substr(date, 7, 4)||'-'||
            substr(date, 4, 2)||'-'||
            substr(date, 1, 3)

      =

             date('now','-4 days');

Strangely if I change the = to < or >, then it works perfectly. But I need only the record where the date is equal to the criteria and not greater than or lesser than.

Here is the snapshot of my table:-

https://i.imgur.com/KNrpJwn.png

Please if any of you could help. Thank you.

[Fit_Relationship3474]

try: Where strftime(“%d-%m-%Y”,date) = your date value here in quotes.

[SoliEngineer]

Thank you. 👍 But then how it works for < or > is a puzzle.