4

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.

kiduxa
  • 3,339
  • 11
  • 37
  • 52
  • 1
    SQLite doesn't use that date format. – Alix Axel Jul 06 '13 at 21:57
  • 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? – kiduxa Jul 06 '13 at 22:02
  • 1
    If you're storing a date as a string, you're doing it wrong. If you're storing a date as a string *in that format*, you're doubly wrong. That format is not sort/comparison/ordering friendly, so any comparisons using greater than or less than will produce incorrect values. Specifically, "01/07/2013" will be less than "02/01/1900", because you're using alphanumeric comparisons, ie. strings. Instead, **you should** store dates as dates, and not as strings! – Lasse V. Karlsen Jul 06 '13 at 22:08
  • This is not really a duplicate of the marked question as this is string comparisons, not datetime comparisons. – Lasse V. Karlsen Jul 06 '13 at 22:14
  • Please use below link to get the answer http://stackoverflow.com/a/43892359/1252158 – Summved Jain May 10 '17 at 12:21
  • Lasse Vagsaether Karlsen - you are doubly wrong, Sqlite does not have a 'date' column type. – Jason Byrd Jan 27 '19 at 20:26

2 Answers2

11

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.

Lasse V. Karlsen
  • 380,855
  • 102
  • 628
  • 825
0

Did you try this :

SELECT strftime('%d/%m/%Y', myDate) as myDate
FROM myTable
WHERE myDate between date('01/07/2013') and date('24/07/2013');
AllTooSir
  • 48,828
  • 16
  • 130
  • 164