Change sort in SQLite

My dates are stored in this format:

3-1-2011
3-15-2011

However, it seems to be gett开发者_如何学Pythoning sorted like this:

3-1-2011
3-10-2011
...
3-19-2011
3-2-2011
3-20-2011
...
3-29-2011
3-3-2011
3-30-2011

So when I try to pull data using WHERE date BETWEEN 3-1-2011 AND 3-3-2011, it appears to return results for everything from 3-1 to 3-29. Without changing all the values, it it possible to fix this?

Your data won't sort as DATEs because you aren't storing them as the SQLite supported format:

YYYY-MM-DD HH:MM:SS.SSS

See the data type documentation for details.

Due to the fact that SQLite doesn't assign any meaning to stored dates (they're simply text, real or integer columns, depending on the format you're using), I don't believe you can easily sort them in the manner you're storing them in. (That said, hopefully someone will prove me wrong.)

As such, two possible solutions would be:

1. Store the dates as timestamps. These would always sort correctly.

2. Use the date and time functions to create a timestamp column on the fly. You could create an alias using the date and time functions with the unixepoch modifier and sort based on this.

I unfortunately don't have access to sqlite at the moment to test these out at the moment, but I think they should be sound. (If not, someone will doubtless correct me.)

SQLite does not have a "type" per column (SQLite is typeless). These are equivalent

create table t(dt datetime)
create table t(dt int)
create table t(dt)

They all create a column dt that will be able to store any data.

The case statement below can be used to turn your [m]m-[d]d-[yy]yy data into a real date, which you can then update back to the table (to standardize) or use for sorting.

select dt, date(
case when dt like '%-%-__' then substr(dt,-2)
     when dt like '%-%-____' then substr(dt,-4) end
|| '-' ||
case when dt like '_-%' then '0' || substr(dt,1,1)
     when dt like '__-%' then substr(dt,1,2) end
|| '-' ||
case when dt like '__-_-%' then '0' || substr(dt,4,1)
      when dt like '__-__-%' then substr(dt,4,2)
      when dt like '_-_-%' then '0' || substr(dt,3,1)
      when dt like '_-__-%' then substr(dt,3,2) end)
from t
order by 2