Unable to filter between two dates in dd-mm-yyyy format in SQLite c#

Jayanta Mukherjee

I'm using SQLite Core .Net framework for database in my c# WPF app and I've added a table in the database.db with multiple columns. One of the columns payDt contains date in dd-mm-yyyy format but since there are no date datatypes in SQLite I've made the datatype for column payDt as text.

Now when I'm trying to filter the table data by date and view it in a datagrid it is not showing anything. I've gone through multiple posts on the internet and have tried the below different approaches but to no avail...

"SELECT * FROM myTable WHERE payDt BETWEEN strftime('%d-%m-%Y','28-02-2022') AND strftime('%d-%m-%Y','25-03-2022')"

"SELECT * FROM myTable WHERE payDt BETWEEN strftime('%Y-%m-%d','2022-02-28') AND strftime('%Y-%m-%d','2022-03-25')"

"SELECT * FROM myTable WHERE payDt BETWEEN '2022-02-28' AND '2022-03-25'"

"SELECT * FROM myTable WHERE payDt BETWEEN '28-02-2022' AND '25-03-2022'"

Please help !!

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jayanta Mukherjee4y ago
@MUHAMMAD IMRAN, Yes I've tried that link, in fact it was one of the first links that I came across but it didn't help. However, recently after further searching I've found a link showing to do the query like
"SELECT * FROM ExcelData WHERE substr(DueDt,7)||substr(DueDt,4,2)||substr(DueDt,1,2) between '20220120' and '20220325'"
It worked ! but when I try to input the startdate & enddate dynamically like say, filter dates between today and next 10 days I don't get any data. I've tried writing queries in 3 different ways shown below
i) "SELECT * FROM ExcelData WHERE substr(DueDt,7)||substr(DueDt,4,2)||substr(DueDt,1,2) between strftime('%Y-%m-%d','now') and strftime('%Y-%m-%d','now','10 days')"

ii) "SELECT * FROM ExcelData WHERE substr(DueDt,7)||substr(DueDt,4,2)||substr(DueDt,1,2) between date('now') and date('now', '+10 days')"

iii) "SELECT * FROM ExcelData WHERE substr(DueDt,7)||substr(DueDt,4,2)||substr(DueDt,1,2) between 'aj' and 'kal'"

Note:
string aj= DateTime.Today.ToString("yyyyMMdd");
string kal=DateTime.Today.AddDays(10).ToString("yyyyMMdd");
How do I get this to work?
0
Muhammad Imran Ansari4y ago
Have you try the following link: hope this will help you. https://stackoverflow.com/questions/29971762/sqlite-database-select-the-data-between-two-dates
0