comparison dates with string dd-mm-yyyy format in sqlite [duplicate]

Question

Im storing my dates as string in format 'dd-mm-yyyy'. Now I want to do a comparison like this:

SELECT strftime('%d/%m/%Y', myDate) as myDate
FROM myTable
WHERE myDate>='01/07/2013' and myDate<='24/07/2013'

But I get nothing. Whats wrong with this query?.

THIS IS NOT A DUPLICATE QUESTION. I was storing everything as String, no as a DATE or DATETIME. I was trying to do a comparison between strings with date format. So is not fair the down vote.

Answer 1

There is no way you can get your query to work like that with your current setup:

I'm storing the date as a String so I guess with the using of strftime I can get what I want. Or am I wrong?

Basically: You're wrong!

The problem here is that a string formatted like that will not be sortable in the way you want.

Specifically:

myDate>='01/07/2013' and myDate<='24/07/2013'

Here, any date that is between the first and the 24th of any month in any year will match this. Ie. this will match: "02/01/1900", as will this: "02/12/2099".

The reason for this is that string comparisons are alphanumerical, not numerical (or datewise). "02" is greater than "01" and less than "24", and the rest is just redundant.

The "logical" way to format a date as a string is to start with the most significant value and work your way downwards, ie. this format: "yyyy/mm/dd", and not the other way around, but the real logical way to store a date, is to store it as a date, and not as a string.

The only way to work with the strings is to convert each string to a date and work with that, but the best way is to fix your schema.

If you absolutely cannot change your current schema, here is a way to make it work, but I do not advice this:

where (substr(myDate, 7, 4) || '-' || substr(myDate, 4, 2) || '-' || substr(myDate, 1, 2)) between '2013-07-01' and '2013-07-24'

This will pick apart the strings, put them together again in the right order, before doing the comparison. Also note the format of the last two dates there.