Date comparison not working when current date is 2 months later to date to compare with

Question

In my app I have to fetch list of all rows if enrollDate is less or equal to current date .

my SQLite query is as

select * from master  where enrollDate<='current date'

enroll date in table is "07/21/2017" and current date is "11/22/2017" .

Everything works well and I get list of rows if current date is till '30/09/2017' but when current date start from "10/01/2017" then list is returned with 0 rows while its also later date.

Please tell me why it is happening and how to resolve it.

it right and also working if it is till 09/30/2017, but if it is from 10/01/2017 to current date as 11/22/2017 then 0 rows returned. format is correct — Neha Shukla, Nov 22 '17 at 06:13

score 1 · Answer 1 · answered Nov 22 '17 at 06:14

You are actually comparing strings, not dates. Strings compare lexicographically.

To compare dates in SQL, consider storing them in a format where lexicographical and temporal ordering are the same. For example, ISO 8601 UTC stamps yyyy-MM-dd or Java's long epoch milliseconds.

Faisal Naseer · Answer 2 · 2017-11-22T08:07:30.687

0

SELECT * FROM master WHERE CAST(strftime('%s', enrollDate)  AS  integer) <=CAST(strftime('%s', currentDate)  AS  integer) ;

edited Nov 22 '17 at 08:07

answered Nov 22 '17 at 06:12

Faisal Naseer

4,110
1
37
55

thanks to you.but still problem is if I out 07/24/2017 in place of enrollDate then it is returning rows but I put column name here as enrollDate then it is returning 0 rows. kindly help – Neha Shukla Nov 22 '17 at 06:25
can you show me the schema of your table? – Faisal Naseer Nov 22 '17 at 07:54
Add some explanation for your answer – Thamaraiselvam Nov 22 '17 at 14:33
sure did nothing just converted String to seconds and then casted it to integer – Faisal Naseer Nov 23 '17 at 06:32

Date comparison not working when current date is 2 months later to date to compare with

2 Answers2